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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
HakimA
Contributor II
Contributor II

Generate dates based on field with condition

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 !

 

 

Labels (5)
1 Solution

Accepted Solutions
rubenmarin1

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;

View solution in original post

5 Replies
rubenmarin1

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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 @rubenmarin1 ?

 

Again thanks for your contribution

rubenmarin1

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
Contributor II
Contributor II
Author

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

 

Thank you