topic How I make a join by 2 different criteria? in QlikView

How I make a join by 2 different criteria?

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: How I make a join by 2 different criteria?

sunny_talwar — Wed, 28 Dec 2016 17:01:14 GMT

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;

Re: How I make a join by 2 different criteria?

Wed, 28 Dec 2016 20:09:39 GMT

Fantastic! Thanks a lot!!!