topic Script repeating a whole table untill today in App Development

Script repeating a whole table untill today

gorterzelf — Sat, 16 Nov 2024 17:29:17 GMT

Hello Qlik Experst,

I have a CSV file, which I would like to put in my script and then repeat the whole XLSX untill today, there is a field Datum in the XLSX this is the startdate. I would like to see in this field the whole table repeated over and over from the startdate till the enddate.

LET vVandaag =Date(Today());

Temp_Bevolkingsopbouw:
LOAD
1 AS Sleutel,
IterNo() as Day,

Date( StartDate + IterNo() - 1 ) as Datum

While StartDate + IterNo() - 1 <= EndDate;

LOAD * INLINE

[StartDate, EndDate

01-01-2020, $(vVandaag)

];

If i load above script i have the dates, but how do i combine that with the XLSX ?

Any help would be great!

Greetings,

Martijn

Re: Script repeating a whole table untill today

JustinDallas — Mon, 15 Feb 2021 14:32:07 GMT

We're probably going to need some dummy data to work with, and what your desired solution looks like.

Re: Script repeating a whole table untill today

gorterzelf — Mon, 15 Feb 2021 14:48:47 GMT

@Justin the input data is in the XLSX and an example of the outcome data of the first 3 dates (in reality till today) is in the XLSX of this message. Hope that is clear?

Thank you for your answer.

Re: Script repeating a whole table untill today

JustinDallas — Mon, 15 Feb 2021 16:18:35 GMT

It looks like you need a Cross Join does the code below exemplify what you are looking for?

Let vStartDate = Num(Date(Today() - 3)) ; Let vVandaag = Num(Date(Today())) ; Temp_Bevolkingsopbouw: Load 1 AS Sleutel, IterNo() as Day, RowNo() as RowNumber, IterNo() as IterNumber, Date($(vStartDate) + IterNo() - 1) as Date AutoGenerate 1 While $(vStartDate) + IterNo() - 1 <= $(vVandaag) ; DataTable: LOAD * Inline [ ID, Provincie, BevolkingOp1Januari_1 15952925, Friesland, 3042 15952991, Overijssel, 5747 36589178, Noord-Holland, 20791 ] ; CrossJoinedTable: JOIN(Temp_Bevolkingsopbouw) LOAD * RESIDENT DataTable ; DROP TABLE DataTable ; EXIT Script ;

Depending on your data and setup, this can cause your QlikSense server to take a long walk and never come back computational-wise.

Re: Script repeating a whole table untill today

gorterzelf — Mon, 15 Feb 2021 19:23:30 GMT

Perfect @JustinDallas you are my hero of the day i got it working and it works fast!

One question how do's the crossjoinedtable work? Is it because there are no fields the same it just combines those to tables in every way posible? Just trying to understand it a bit.

Re: Script repeating a whole table untill today

JustinDallas — Mon, 15 Feb 2021 20:57:55 GMT

Yes. If no field is common between both tables, the system will automatically do a Cross Join. Your LEFT and RIGHT joins depend on at least one field being common to work.

Re: Script repeating a whole table untill today

manoranjan_d — Mon, 15 Feb 2021 21:00:25 GMT

Hi,

Try to use master calendar concept in the script which will link your xcel file.

Re: Script repeating a whole table untill today

gorterzelf — Mon, 15 Feb 2021 21:03:45 GMT

Perfect! Thanks again!

Re: Script repeating a whole table untill today

gorterzelf — Mon, 15 Feb 2021 21:06:57 GMT

Thanks, i got it working for now i used master calendars before, the solution from Justin works fast, so i will use this for now. Thanks again for thinking a long with me, much appreciated.

Greetings Martijn