Re: DATA LOAD - Qlik Community

ankit777 · ‎2014-03-27

Hi all,

I have a table with two columns. Date and score.

I want to load the data 15 days prior to the max date in my table.

I.e if max date in my table is 28/5/2013 then data should be loaded only till 13/15/2013.

What code should I write in my script?

datanibbler · ‎2014-03-27

Hi Ankit,

I don't know by heart, but:

- First try out if you can load the max._value of your date field (using e.g. max(date) or LASTVALUE(date));

- Then construct a WHERE_clause along these lines:

>>> WHERE [date] >= ([max(date)] - 15) <<<

HTH

Best regards,

DataNibbler

Aurelien_Martinez · ‎2014-03-27

Hi,

Try :

TMP:

LOAD * FROM table ORDER BY Date desc;

LET vMaxDate = Peek('table', 0, 'Date');

LET vDateMin = Date($(vMaxDate) -15 );

NoConcatenate

DATA:

LOAD *

FROM <table>

WHERE Date >= $(vDateMin)

;

drop table TMP;

Aurélien.

Help users find answers! Don't forget to mark a solution that worked for you!

MK_QSL · ‎2014-03-27

Like this?

Temp:

Load Date(Date) as Date, Score Inline

[

Date, Score

01/03/2014, 12

02/03/2014, 15

03/03/2014, 16

04/03/2014, 18

05/03/2014, 23

06/03/2014, 24

07/03/2014, 26

08/03/2014, 30

09/03/2014, 32

10/03/2014, 11

11/03/2014, 25

12/03/2014, 15

13/03/2014, 45

14/03/2014, 25

15/03/2014, 26

16/03/2014, 21

17/03/2014, 24

18/03/2014, 26

19/03/2014, 45

20/03/2014, 47

21/03/2014, 58

22/03/2014, 86

23/03/2014, 98

24/03/2014, 45

25/03/2014, 25

26/03/2014, 25

27/03/2014, 45

];

Join

Temp2:

Load Date(Max(Date)) as MaxDate Resident Temp;

NoConcatenate

Final:

Load Date, Score

Resident Temp

Where Num(Date) > Num(MaxDate-15)

Order By Date;

Drop Table Temp;

maxgro · ‎2014-03-27

another way

using Manish data (thanks)

Table:

Load Date, Score Inline

[