Script repeating a whole table untill today

[gorterzelf]

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

[JustinDallas]

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

[gorterzelf]

@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.

[JustinDallas · Accepted solution]

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.

[gorterzelf]

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. 

[JustinDallas]

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. 

[manoranjan_d]

Hi,

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

[gorterzelf]

Perfect! Thanks again!

[gorterzelf]

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