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

[omkar_2611]

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?

[Dalton_Ruer]

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]

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;

[omkar_2611 · Accepted solution]

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;