Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Madina
Contributor III
Contributor III

Add row with previous month of min month date in load script

Hello,

I have a table like below

sea:

LOAD
Date(data , 'DD.MM.YYYY') AS data ,

id,

name

FROM [lib://qwerty/file.xlsx]
(ooxml, embedded labels, table is Seas);

 

What I want is to add empty row to this table, with only date written in data. And what is needed is to write date on one month less that in original table. 

For example, if in original table sea, my minimum date is  01.01.2020 , I want to add row where date will be 01.12.2019. And other columns in that row is just empty

I tried to have a variable 

let mindate = Num(MakeDate(Year(Min(dateid)), Month(Min(dateid)), 1));

And just add month less from that to other table and concatenate, but dont know how to get previous month

Thanks in advance

 

Regards, Madina
Labels (5)
2 Replies
Ahidhar
Creator III
Creator III

try something like this , you can autogenerate as many as you want

tab:
load * Inline
[
date,id
01/5/2020,1
01/4/2020,2
01/3/2020,3
01/2/2020,4
01/1/2020,5
];

let mindate=num(peek('date',-1,'tab'));
join(tab)
tab1:
load
date(AddMonths($(mindate),-RowNo()),'DD/MM/YYYY') as date
AutoGenerate 100;

anat
Master
Master

sea:

LOAD
Date(data , 'DD.MM.YYYY') AS data ,

id,

name

FROM [lib://qwerty/file.xlsx]
(ooxml, embedded labels, table is Seas);

concatenate(sea)

LOAD
Date(addmonths(data,-1) , 'DD.MM.YYYY') AS data ,

'' as id,

'' as name

resident sea;