Generate dates based on field with condition

[HakimA]

Hello Qlik community,

I am struggling a bit with a data transformation I need to perform.

I have a table like this one

start_date	division	service	ID
01/01/2025	EMEA	AAA	AHEMA
01/03/2025	APAC	BBB	AHEMA
01/04/2025	EMEA	CCC	AHEMA
01/01/2025	LAM	DDD	KILMA
01/05/2025	NAM	EEE	KILMA

What I need to get is this table below with a new field Period 

start_date	division	service	ID	Period
01/01/2025	EMEA	AAA	AHEMA	202501
01/01/2025	EMEA	AAA	AHEMA	202502
01/03/2025	APAC	BBB	AHEMA	202503
01/04/2025	EMEA	CCC	AHEMA	202504
01/04/2025	EMEA	CCC	AHEMA	202505
01/01/2025	LAM	DDD	KILMA	202501
01/01/2025	LAM	DDD	KILMA	202502
01/01/2025	LAM	DDD	KILMA	202503
01/01/2025	LAM	DDD	KILMA	202504
01/05/2025	NAM	EEE	KILMA	202505

I've tried something like that but it is not working as it is ignoring the change of division/service

LET vMaxDate =num(monthstart(today()));

LOAD distinct

start_date,
division,
service,
DATE(MonthStart(start_date+ IterNo() - 1),'YYYYMM') AS Period,
ID
FROM [lib://AttachedFiles/Classeur1.xlsx]
(ooxml, embedded labels, table is Feuil1)
WHILE start_date+ IterNo() - 1 < $(vMaxDate);

I am missing something here but can't figure it out.

Thanks for any help !

[rubenmarin · Accepted solution]

Hi, you can get that table with this script

// Load data, with extra field to check loaded keys
tmpData:
LOAD Date(start_date) as start_date, division, service, ID,
	start_date &'_'& ID as tmpLoadedKey
INLINE [
    start_date, division, service, ID
    01/01/2025, EMEA, AAA, AHEMA
    01/03/2025, APAC, BBB, AHEMA
    01/04/2025, EMEA, CCC, AHEMA
    01/01/2025, LAM, DDD, KILMA
    01/05/2025, NAM, EEE, KILMA
];

// Generate missing dates
tmpGenerateKeys:
NoConcatenate
LOAD 
	 AddMonths(MinDate, IterNo()-1) 			as start_date
While AddMonths(MinDate, IterNo()-1) <= MaxDate
;
LOAD 
	 MonthStart(Min(FieldValue('start_date', RecNo()))) 	as MinDate,
	 MonthStart(Max(FieldValue('start_date', RecNo()))) 	as MaxDate
AutoGenerate FieldValueCount('start_date');

// Cartesian with ID
Outer Join (tmpGenerateKeys)
LOAD Distinct ID Resident tmpData;

// Add missing keys to data
Concatenate (tmpData)
LOAD * Resident tmpGenerateKeys
Where not Exists('tmpLoadedKey',start_date &'_'& ID);

DROP Table tmpGenerateKeys;

// Final table filling data:
Data:
LOAD 
	start_date,
	If(Peek('ID')=ID and IsNull(division)
	  ,Peek(division)
	  ,division) as division,
	If(Peek('ID')=ID and IsNull(service)
	  ,Peek(service)
	  ,service) as service,
	ID,
	Date(start_date,'YYYYMM') as Period
Resident
	tmpData
Order By
	ID,start_date
;

DROP table tmpData;

[HakimA]

Hello Rubenmarin

Thanks for the reply. I'm going to test this and give you a feedback.

Meanwhile I was able to find another solution that i'll share here also

Best regards

[HakimA]

The solution I found on my side is this one

// Load data
tmpData:
LOAD Date(start_date) as start_date, 
division, 
service, 
ID
INLINE [
    start_date, division, service, ID
    01/01/2025, EMEA, AAA, AHEMA
    01/03/2025, APAC, BBB, AHEMA
    01/04/2025, EMEA, CCC, AHEMA
    01/01/2025, LAM, DDD, KILMA
    01/05/2025, NAM, EEE, KILMA
];



NoConcatenate
tmpData1:
LOAD distinct
start_date,
division, 
service, 
ID
resident tmpData
order by ID,start_date desc;
drop table tmpData;

NoConcatenate
tmpData2:
LOAD distinct
start_date,
division, 
service, 
ID,
    if(peek(ID)<>ID,date(monthstart(today())-1),date(peek(start_date)-1)) as end_date
resident tmpData1;
drop table tmpData1;


NoConcatenate

tmpData3:
LOAD distinct
start_date,
division, 
service, 
ID,
    DATE(MonthStart(start_date + IterNo() - 1),'YYYYMM') AS Period
resident tmpData2

WHILE start_date + IterNo() - 1 < end_date


;
drop table tmpData2;

What do you think about that @rubenmarin ?

Again thanks for your contribution

[rubenmarin]

Good solution, in fact I didn't see that start_date was propagated and I created consecutive values for that column, it wouldn't be difficult to adjust it

[HakimA]

At the end start_date is not that important as we have the Period calculated correctly !

Thank you