Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm new in Qlikview, I have the following problem and I would like if someone can suggest me how to solve it, please.
I have the following QVDs:
STORBOX_TABLE.QVD
Invoice_num | Barcode | Num_Vendor | Invoice_Date | File_Name |
---|---|---|---|---|
100 | 100 | A0001 | 31-12-1015 | D00010.PDF |
200 | 200 | A0001 | 31-01-1015 | D00030.PDF |
300 | 250 | A0002 | 31-01-1015 | GFR040.PDF |
400 | 450 | A0003 | 31-03-1015 | GFR020.PDF |
ETL_PAYABLES.QVD
Invoice_ID | Invoice_num | Barcode | Num_Vendor | Creation_Date | Invoice_Amount | Invoice_Currency | Invoice_Type |
---|---|---|---|---|---|---|---|
45689 | 100 | 100 | A0001 | 01-01-2016 | 500 | USD | Standard |
44569 | 200 | 800 | A0001 | 03-02-2015 | 100 | USD | Standard |
23569 | 600 | 250 | A0002 | 02-02-2015 | 200 | USD | Standard |
4589 | 700 | 700 | A0003 | 01-01-2015 | 800 | USD | Standard |
The issue is that I have two citeria in order to make the join:
Criteria 1: Invoice_Num + Num_Vendor
Criteria 2: Barcode + Num_Vendor
The idea is to get the result of a table like this (keeping the complete table STORBOX_TABLE.QVD):
STORBOX_VS_ETL
Invoice_num | Barcode | Num_Vendor | Invoice_Date | File_Name | Invoice_ID | Invoice_Amount | Invoice_Currency |
---|---|---|---|---|---|---|---|
100 | 100 | A0001 | 31-12-1015 | D00010.PDF | 45689 | 500 | USD |
200 | 200 | A0001 | 31-01-1015 | D00030.PDF | 44569 | 100 | USD |
300 | 250 | A0002 | 31-01-1015 | GFR040.PDF | 23569 | 200 | USD |
400 | 450 | A0003 | 31-03-1015 | GFR020.PDF |
As you can see, the first record is the result of criteria 1 and criteria 2 because the Invoice_Num, Barcode and Num_Vendor are the same in both tables.
But the second record is the result of criteria 1, Invoice_Num 200 and Num_Vendor A0001 (but Barcode is different)
In addition, the third record is the result of criteria 2, Barcode 250 and Num_Vendor A0002 (but Invoice_Num is different)
This situation can be modeled in Qlikview? Can you help me with any ideas?
Thank you very much!
Something like this:
STOREBOX_TABLE:
LOAD * INLINE [
Invoice_num, Barcode, Num_Vendor, Invoice_Date, File_Name
100, 100, A0001, 31-12-1015, D00010.PDF
200, 200, A0001, 31-01-1015, D00030.PDF
300, 250, A0002, 31-01-1015, GFR040.PDF
400, 450, A0003, 31-03-1015, GFR020.PDF
];
ETL_PAYABLES:
LOAD * INLINE [
Invoice_ID, Invoice_num, Barcode, Num_Vendor, Creation_Date, Invoice_Amount, Invoice_Currency, Invoice_Type
45689, 100, 100, A0001, 01-01-2016, 500, USD, Standard
44569, 200, 800, A0001, 03-02-2015, 100, USD, Standard
23569, 600, 250, A0002, 02-02-2015, 200, USD, Standard
4589, 700, 700, A0003, 01-01-2015, 800, USD, Standard
];
Left Join (STOREBOX_TABLE)
LOAD Invoice_ID,
Invoice_num,
Num_Vendor,
Creation_Date,
Invoice_Amount,
Invoice_Currency,
Invoice_Type
Resident ETL_PAYABLES;
Left Join (STOREBOX_TABLE)
LOAD Invoice_ID as Invoice_ID1,
Barcode,
Num_Vendor,
Creation_Date as Creation_Date1,
Invoice_Amount as Invoice_Amount1,
Invoice_Currency as Invoice_Currency1,
Invoice_Type as Invoice_Type1
Resident ETL_PAYABLES;
DROP Table ETL_PAYABLES;
FINAL_STOREBOX_TABLE:
LOAD Invoice_num,
Barcode,
Num_Vendor,
Invoice_Date,
File_Name,
If(Len(Trim(Invoice_ID)) = 0, Invoice_ID1, Invoice_ID) as Invoice_ID,
If(Len(Trim(Creation_Date)) = 0, Creation_Date1, Creation_Date) as Creation_Date,
If(Len(Trim(Invoice_Amount)) = 0, Invoice_Amount1, Invoice_Amount) as Invoice_Amount,
If(Len(Trim(Invoice_Currency)) = 0, Invoice_Currency1, Invoice_Currency) as Invoice_Currency,
If(Len(Trim(Invoice_Type)) = 0, Invoice_Type1, Invoice_Type) as Invoice_Type
Resident STOREBOX_TABLE;
DROP Table STOREBOX_TABLE;
Something like this:
STOREBOX_TABLE:
LOAD * INLINE [
Invoice_num, Barcode, Num_Vendor, Invoice_Date, File_Name
100, 100, A0001, 31-12-1015, D00010.PDF
200, 200, A0001, 31-01-1015, D00030.PDF
300, 250, A0002, 31-01-1015, GFR040.PDF
400, 450, A0003, 31-03-1015, GFR020.PDF
];
ETL_PAYABLES:
LOAD * INLINE [
Invoice_ID, Invoice_num, Barcode, Num_Vendor, Creation_Date, Invoice_Amount, Invoice_Currency, Invoice_Type
45689, 100, 100, A0001, 01-01-2016, 500, USD, Standard
44569, 200, 800, A0001, 03-02-2015, 100, USD, Standard
23569, 600, 250, A0002, 02-02-2015, 200, USD, Standard
4589, 700, 700, A0003, 01-01-2015, 800, USD, Standard
];
Left Join (STOREBOX_TABLE)
LOAD Invoice_ID,
Invoice_num,
Num_Vendor,
Creation_Date,
Invoice_Amount,
Invoice_Currency,
Invoice_Type
Resident ETL_PAYABLES;
Left Join (STOREBOX_TABLE)
LOAD Invoice_ID as Invoice_ID1,
Barcode,
Num_Vendor,
Creation_Date as Creation_Date1,
Invoice_Amount as Invoice_Amount1,
Invoice_Currency as Invoice_Currency1,
Invoice_Type as Invoice_Type1
Resident ETL_PAYABLES;
DROP Table ETL_PAYABLES;
FINAL_STOREBOX_TABLE:
LOAD Invoice_num,
Barcode,
Num_Vendor,
Invoice_Date,
File_Name,
If(Len(Trim(Invoice_ID)) = 0, Invoice_ID1, Invoice_ID) as Invoice_ID,
If(Len(Trim(Creation_Date)) = 0, Creation_Date1, Creation_Date) as Creation_Date,
If(Len(Trim(Invoice_Amount)) = 0, Invoice_Amount1, Invoice_Amount) as Invoice_Amount,
If(Len(Trim(Invoice_Currency)) = 0, Invoice_Currency1, Invoice_Currency) as Invoice_Currency,
If(Len(Trim(Invoice_Type)) = 0, Invoice_Type1, Invoice_Type) as Invoice_Type
Resident STOREBOX_TABLE;
DROP Table STOREBOX_TABLE;
Fantastic! Thanks a lot!!!