Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Learner11
Contributor II
Contributor II

Joining on different columns

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
Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

@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;

View solution in original post

3 Replies
a_mullick
Creator III
Creator III

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:

a_mullick_0-1682587653094.png

 

 

Kushal_Chawda

@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;
Qlik_Learner11
Contributor II
Contributor II
Author

Thanks so much  and  A_mullick. you are great!