Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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;
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;
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
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
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
At the end start_date is not that important as we have the Period calculated correctly !
Thank you