Identifying Missing Entries, Additional Entries, and differences

[ramsqlik]

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

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output:

[ramsqlik]

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

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

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

[ramsqlik]

Thanks