topic Re: Joining on different columns in QlikView

Joining on different columns

Qlik_Learner11 — Thu, 27 Apr 2023 03:05:34 GMT

Please help me with my requirement.

I have below table.

T1:
LOAD * Inline[
g_date, No_of_Reqs
4/1/2023, 10
4/2/2023, 20
4/3/2023, 30
4/4/2023, 40
4/5/2023, 50
4/6/2023, 60
4/7/2023, 70
4/8/2023, 80
4/9/2023, 90
4/10/2023, 100 ];

I need to calculate g_date-5 and get No_of_Reqs for g_date-5. I created T2 as below and tried multiple join conditions but not able to achieve the desired output as Qlik joins only on common columns.

T2:

LOAD
g_date,
Date(log_date - 5) as g_date_new
Resident T1;

Expected Output:

g_date	no_of_requests	g_date_new	no_of_requests
4/1/2023	10	3/27/2023
4/2/2023	20	3/28/2023
4/3/2023	30	3/29/2023
4/4/2023	40	3/30/2023
4/5/2023	50	3/31/2023
4/6/2023	60	4/1/2023	10
4/7/2023	70	4/2/2023	20
4/8/2023	80	4/3/2023	30
4/9/2023	90	4/4/2023	40
4/10/2023	100	4/5/2023	50

Re: Joining on different columns

a_mullick — Thu, 27 Apr 2023 09:28:28 GMT

Hi,

Does this work for you?

T1:
Load
*,
Date(g_date - 5) as g_date_5;
LOAD * Inline[
g_date, No_of_Reqs
4/1/2023, 10
4/2/2023, 20
4/3/2023, 30
4/4/2023, 40
4/5/2023, 50
4/6/2023, 60
4/7/2023, 70
4/8/2023, 80
4/9/2023, 90
4/10/2023, 100 ];

T2:
LOAD
g_date as g_date_5,
No_of_Reqs as No_of_Reqs_5
Resident T1;

Simple table output:

Re: Joining on different columns

Kushal_Chawda — Thu, 27 Apr 2023 10:52:12 GMT

@Qlik_Learner11 use below to avoid any additional data to be populated

Data: LOAD *,date(g_date-5) as g_date_5 Inline [ g_date, No_of_Reqs 4/1/2023, 10 4/2/2023, 20 4/3/2023, 30 4/4/2023, 40 4/5/2023, 50 4/6/2023, 60 4/7/2023, 70 4/8/2023, 80 4/9/2023, 90 4/10/2023, 100 ]; Left Join LOAD g_date as g_date_5, No_of_Reqs as g_date_5_No_of_Reqs Resident Data;

Re: Joining on different columns

Qlik_Learner11 — Thu, 27 Apr 2023 14:30:35 GMT

Thanks so much Kushal_Chawda and A_mullick. you are great!