Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramsqlik
Contributor
Contributor

Identifying Missing Entries, Additional Entries, and differences

GST_DIFF.JPGI 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

Labels (2)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

6 Replies
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:

commQV77.PNG

ramsqlik
Contributor
Contributor
Author

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

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.

commQV78.PNG

ramsqlik
Contributor
Contributor
Author

Thanks