Finding differences with QlikView.

After various months checking synchronization data I have done a report that will tell me which information is present in one side and not in the other. After creating that, I needed to check why the hell the info didn't went to the other system. After all the analysis I had a list with the items that had problems.

Good. Now what? Export to excel and create the query. These days are gone for me!!

What:
Create the search query based on a list box selection of the report.

How:

• I have made an inline load with all the queries I use to check (all the tables as well) info separated by System and subsystem.

LOAD * INLINE [
    SYSTEM,  SUBSYSTEM, QUERY
    SENDER,   INVOICES, "select *   from INVOICES a,  INVOICES_BODY b where a.invoice_num = b.a.invoice_num and a.invoice_num in ( "
    RECEIVER, INVOICES, "select *   from usr_inv.inv_data a,  usr_data.invoices b  where a.code = b.code    and b.code in ( "
 ];

• Loaded the invoice nr for both system in the same table, having a column called system with the system name RECEIVER and SENDER;
• Created two tables, one only with RECEIVER and other with SENDER data using set analisys on expression  ( count ( {<SYSTEM= {'SENDER'}>} distinct INV_NR ) ) and the SUBSYTEM as dimension;
• Created one calculated list (Nr of Systems) with the number of system that an Invoice is present ( =aggr(count(distinct SUBSYSTEM) , INV_NR )  ). As we have 2 systems here, it should show 1 for invoices only on one system and 2 for both systems;
• Created a filter for the SUBSYSTEM;
• One textbox that will convert the list to string and merge with the query ( =Only(QUERY) & Concat(CODREALIZACAO,',') &' )'   ). This will only be shown if I have selected a Subsystem and Nr of Systems.

When clicking the list of number of system, it will show me the amount I have in one side and not on another. Clicking on System, will show my List Box and will show me the query I need to executed over a PL/SQL Program.

That´s it. The versatility of Qlikview, it´s clicking options and the possibility of loading from multiple sources.