Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
omkar_2611
Partner - Contributor II
Partner - Contributor II

How to add a separated fields to a table based on another tables values?

Hello,

I am new to Qlik Sense. I am trying to implement a logic into qlik sense but I am stuck as I don't have good knowledge in Qlik Script.

I have following tables:

Table A:

key from
1 01.01.2022
2 01.06.2022

 

Table B:

key till
1 31.05.2022
2 31.12.2022

 

Output:

key month
1 01.01.2022
1 01.02.2022
1 01.03.2022
1 01.04.2022
1 01.05.2022
2 01.06.2022
2 01.07.2022
2 01.08.2022
2 01.09.2022
2 01.10.2022
2 01.11.2022
2 01.12.2022

 

I was able to do get the solution if the 'from' and 'till' was in same table, using preceding load

Load *,
Date(addmonths(from,IterNo()-1)) as month
while Date(addmonths(from,IterNo()-1)) <= till;

 

How can I achieve the same output if 'from' and 'till' are in different tables?
Labels (2)
1 Solution

Accepted Solutions
omkar_2611
Partner - Contributor II
Partner - Contributor II
Author

Solution

Table_A:
LOAD * Inline [
key, from
1, 01.01.2022
2, 01.06.2022
];

join(Table_A)
Table_B:

LOAD * Inline [
key, till
1, 31.05.2022
2, 31.12.2022
];

Table_C:
LOAD key,
Date(addmonths(from,IterNo()-1)) as month
while Date(addmonths(from,IterNo()-1)) <= till;
LOAD * Resident Table_A;

View solution in original post

3 Replies
Dalton_Ruer
Support
Support

Simply JOIN the table b into table A so you have from/to in the same table. 

I'm lost on what you showed as the output since table b seemse to be d/m/y format because of the 31 in the first field for both rows. 

Aurelien_Martinez
Partner - Specialist II
Partner - Specialist II

Hi,

With the intervalmatch function :

from:
Load
  key,
  Date#(from, 'DD.MM.YYYY') as from
Inline [
key,	from
1,	01.01.2022
2,	01.06.2022
];

left Join(from)
Load
  key,
  Date#(till, 'DD.MM.YYYY') as till
Inline [
key,	till
1,	31.05.2022
2,	31.12.2022
];

_tmp_minmax:
Load
  Min(from)*1 as min,
  Max(till)*1 as max
Resident from
;

Let vMin = Peek('min', 0, '_tmp_minmax')*1;
Let vMax = Peek('max', 0, '_tmp_minmax')*1;

Trace $(vMax) $(vMin);
Drop Table _tmp_minmax;

_tmp_cal:
Load Distinct
  MonthStart($(vMin) + RecNo() - 1) as Month
AutoGenerate($(vMax) - $(vMin) + 1)
;

Left Join(from)
IntervalMatch(Month)
Load
  from,
  till
Resident from
;

Drop Table _tmp_cal;
Drop Fields from, till;

 

Help users find answers! Don't forget to mark a solution that worked for you!
omkar_2611
Partner - Contributor II
Partner - Contributor II
Author

Solution

Table_A:
LOAD * Inline [
key, from
1, 01.01.2022
2, 01.06.2022
];

join(Table_A)
Table_B:

LOAD * Inline [
key, till
1, 31.05.2022
2, 31.12.2022
];

Table_C:
LOAD key,
Date(addmonths(from,IterNo()-1)) as month
while Date(addmonths(from,IterNo()-1)) <= till;
LOAD * Resident Table_A;