Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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)
6 Replies
saran7de
Master
Master

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;
saran7de
Master
Master

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

saran7de
Master
Master

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;
saran7de
Master
Master

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

commQV78.PNG

ramsqlik
Contributor
Contributor
Author

Thanks Saran for your detailed inputs. Appreciate.  I am trying to port this code to my large data and trying to understand the way it works. I am beginer so trying to learn from experts like you. Appreciate your help

There are three possible entry erros  which we are trying to solve through qlikview.

Summary:

T1  Table contains all the Purchase bills this customer collected and entered in the excel

T2 Tabe contains all the GSTRN filed by the Vendor to GSTRIN Portal.

T3  Table contains all the GSTRN filed by this customer to GSTRIN Portal and claim GST 

 

The possible gaps in the  manual process are below

T1 Purchase BillsT2 Vendor Filed GSTT3 Actual  Filed GST & ClaimedRemarks
YesYesYesPerfect. No issues
NoYesNoT1.T3.Missing
YesNoYesT2. Missing
YesYesNoT3.Missing
YesNoNoT2.T3.Missing
NoNoNoNot Applicable. No way to find

 

So we may have to fine tune above code. Request your help

I have updated the examples excel file and uploaded for easy to identify.