topic Identifying Missing Entries, Additional Entries, and differences in QlikView

Identifying Missing Entries, Additional Entries, and differences

ramsqlik — Mon, 19 Jul 2021 21:18:51 GMT

I have 3 excel sheets which contains large amount of data. Sample shown above ( I can combine all the three sheets into Single sheet as the Field names are common for all the three sheets)

Namely:

T1.PURCHAS_BILLS.XLS : This sheet contains All purchase bills as per accounting books

T2.VENDOR_SUBMITTED_GSTR.XLS : This sheet contains All vendors submitted bills to GSTRN Portal

T3.ACTUAL_FILED_GSTR.XLS: This sheet contains actual filing submitted by owner to GSTRN Portal

Some times the vendor will missed to submit GSTRN details to GSTRN Portal. Some times the actual owner missed to submit GSTRN details to GSTRN Portal

I need to identify the missing entries. For easy visualisation, I have manually sorted and highlighted as DIFFERNCES

Is there any way I can take these data into Qlikview and identify missing entries?

Thank you

Re: Identifying Missing Entries, Additional Entries, and differences

Saravanan_Desingh — Tue, 20 Jul 2021 03:04:39 GMT

One solution is,

tab1: LOAD *, 'T1' As Flag, GSTIN&'@'&[Vendor Name]&'@'&[Invoice Date] As Key; LOAD * INLINE [ No, GSTIN, Vendor Name, Invoice Date 1, 27AAA, Max India Pvt Ltd, 3/4/2020 2, 33AABF, Safron Transport, 5/4/2020 3, 33AASC, Arun Trading, 10/4/2020 4, 33AABF, Safron Transport, 11/4/2020 ]; tab2: LOAD *, 'T2' As Flag, GSTIN&'@'&[Vendor Name]&'@'&[Invoice Date] As Key; LOAD * INLINE [ No, GSTIN, Vendor Name, Invoice Date 1, 27AAA, Max India Pvt Ltd, 3/4/2020 2, 33AABF, Safron Transport, 5/4/2020 3, 33AASC, Arun Trading, 10/4/2020 ]; tab3: LOAD *, 'T3' As Flag, GSTIN&'@'&[Vendor Name]&'@'&[Invoice Date] As Key; LOAD * INLINE [ No, GSTIN, Vendor Name, Invoice Date 1, 33AASC, Arun Trading, 10/4/2020 2, 33AABF, Safron Transport, 11/4/2020 ]; Result: NoConcatenate LOAD Key, PurgeChar('T1T2T3',Concat(DISTINCT Flag)) As Result Resident tab1 Group By Key; Left Join(Result) LOAD Key, If(Len(Trim(Result))>0,'T'&Result&'.MISSING') As DIFFERENCES Resident Result;

Re: Identifying Missing Entries, Additional Entries, and differences

Saravanan_Desingh — Tue, 20 Jul 2021 03:05:12 GMT

Output:

Re: Identifying Missing Entries, Additional Entries, and differences

ramsqlik — Tue, 20 Jul 2021 16:14:20 GMT

Thanks for your help and appreciate. My applogies if I ask some novice question as I am trying to learn Qlikview and unfamiliar with all advanced scripting.

Sorry to make you to create the entire data set. My applogies.

I have added 3 excel file so that you can look at the data easily.

DROPBOX SHARE

I am trying to recreate your script as below

tab1: LOAD'T1' As Flag, No, GSTIN, MM_YYYY, VENDOR_NAME, INVOICE_DATE as Key, INVOICE_AMOUNT, TAXABLE_VALUE, IGST, CGST, SGST, CESS, BILL_TYPE FROM [C:\Temp\Qlik\T1.PURCHAS_BILLS.xlsx] (ooxml, embedded labels) ; tab2: LOAD 'T2' As Flag, No, GSTIN, MM_YYYY, VENDOR_NAME, INVOICE_DATE as Key, INVOICE_AMOUNT, TAXABLE_VALUE, IGST, CGST, SGST, CESS, BILL_TYPE FROM [C:\Temp\Qlik\T2.VENDOR_SUBMITTED_GSTR.xlsx] (ooxml, embedded labels, table is Sheet1); tab3: LOAD 'T3' As Flag, No, GSTIN, MM_YYYY, VENDOR_NAME, INVOICE_DATE as Key, INVOICE_AMOUNT, TAXABLE_VALUE, IGST, CGST, SGST, CESS, BILL_TYPE FROM [C:\Temp\Qlik\T3.ACTUAL_FILED_GSTR.xlsx] (ooxml, embedded labels, table is Sheet1); Result: NoConcatenate LOAD Key, PurgeChar('T1T2T3',Concat(DISTINCT Flag)) As Result Resident tab1 Group By Key; Left Join(Result) LOAD Key, If(Len(Trim(Result))>0,'T'&Result&'.MISSING') As DIFFERENCES Resident Result;

For Final Result Table, what are the dimensions, and expressions we should assign?

Thanks

Re: Identifying Missing Entries, Additional Entries, and differences

Saravanan_Desingh — Tue, 20 Jul 2021 17:53:38 GMT

Try this:

tab1: LOAD *, 'T1' As Flag, GSTIN&'@'&MM_YYYY&'@'&VENDOR_NAME&'@'&INVOICE_DATE As Key; LOAD No, GSTIN, MM_YYYY, VENDOR_NAME, INVOICE_DATE, INVOICE_AMOUNT, TAXABLE_VALUE, IGST, CGST, SGST, CESS, BILL_TYPE FROM [C:\Users\sarav\Downloads\Qlik\T1.PURCHAS_BILLS.xlsx] (ooxml, embedded labels, table is Sheet1); LOAD *, 'T2' As Flag, GSTIN&'@'&MM_YYYY&'@'&VENDOR_NAME&'@'&INVOICE_DATE As Key; LOAD No, GSTIN, MM_YYYY, VENDOR_NAME, INVOICE_DATE, INVOICE_AMOUNT, TAXABLE_VALUE, IGST, CGST, SGST, CESS, BILL_TYPE FROM [C:\Users\sarav\Downloads\Qlik\T2.VENDOR_SUBMITTED_GSTR.xlsx] (ooxml, embedded labels, table is Sheet1); LOAD *, 'T3' As Flag, GSTIN&'@'&MM_YYYY&'@'&VENDOR_NAME&'@'&INVOICE_DATE As Key; LOAD No, GSTIN, MM_YYYY, VENDOR_NAME, INVOICE_DATE, INVOICE_AMOUNT, TAXABLE_VALUE, IGST, CGST, SGST, CESS, BILL_TYPE FROM [C:\Users\sarav\Downloads\Qlik\T3.ACTUAL_FILED_GSTR.xlsx] (ooxml, embedded labels, table is Sheet1); Result: NoConcatenate LOAD Key, PurgeChar('T1T2T3',Concat(DISTINCT Flag)) As Result Resident tab1 Group By Key; Left Join(Result) LOAD Key, If(Len(Trim(Result))>0,'T'&Result&'.MISSING') As DIFFERENCES Resident Result; Output: NoConcatenate LOAD * Resident tab1 Where Flag='T1' ; Left Join(Output) LOAD Key, If(Len(Trim(Result))>0,'T'&Result&'.MISSING') As DIFFERENCES Resident Result; Drop Table Result, tab1;

Re: Identifying Missing Entries, Additional Entries, and differences

Saravanan_Desingh — Tue, 20 Jul 2021 17:54:41 GMT

Chart is not needed. The Below Output uses 'Table Box' Object.

Re: Identifying Missing Entries, Additional Entries, and differences

ramsqlik — Sat, 24 Jul 2021 10:35:45 GMT

Thanks