topic Re: DATA LOAD in QlikView

DATA LOAD

ankit777 — Thu, 27 Mar 2014 14:42:04 GMT

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?

Re: DATA LOAD

datanibbler — Thu, 27 Mar 2014 14:52:33 GMT

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

Re: DATA LOAD

Aurelien_Martinez — Thu, 27 Mar 2014 14:52:40 GMT

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.

Re: DATA LOAD

MK_QSL — Thu, 27 Mar 2014 14:56:16 GMT

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;

Re: DATA LOAD

maxgro — Thu, 27 Mar 2014 23:18:34 GMT

another way

using Manish data (thanks)

Table:

Load 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

];

Tmp:

Load Max(Date) as MaxDate Resident Table;

vMaxDate = Peek('MaxDate');

DROP table Tmp;

right keep (Table) // keep only last 15 dates

Load Date Resident Table

where Date >= $(vMaxDate)+1-15;