topic Re: record for single date in QlikView

record for single date

Fri, 24 Apr 2015 11:45:32 GMT

Hi Experts,

I have sales records for april month in a qvd.

I want to pick up the sales data for a single date.

for example, the qvd has date field starting from 01/04/2015 to 24/04/2015. Now i have to pick up sales records only for 22/04/2015.

How can i do that.

I have tried using match(), wildmatch(). but its not working.

Please give some suggestions.

Re: record for single date

robert_mika — Fri, 24 Apr 2015 11:49:22 GMT

How does your data looks like?

If you choose the Date from Listbox what is happening?

Do you need to pick the day in front end or in script?

Re: record for single date

ankitaag — Fri, 24 Apr 2015 11:53:09 GMT

T1:

LOAD * INLINE [

Date, Data

01-04-2015, 96

02-04-2015, 98

03-04-2015, 17

04-04-2015, 43

05-04-2015, 60

06-04-2015, 23

07-04-2015, 94

08-04-2015, 47

09-04-2015, 96

10-04-2015, 39

11-04-2015, 70

12-04-2015, 54

13-04-2015, 24

14-04-2015, 69

15-04-2015, 83

16-04-2015, 68

17-04-2015, 68

18-04-2015, 77

19-04-2015, 42

20-04-2015, 53

21-04-2015, 21

22-04-2015, 31

23-04-2015, 98

24-04-2015, 73

];

Store T1 into path\T1.qvd (qvd);

T2:

LOAD Date,

Data

FROM

[path\T1.qvd]

(qvd)

where Date(Date,'DD/MM/YYYY')='22/04/2015';

Re: record for single date

Fri, 24 Apr 2015 11:59:52 GMT

hi Robert,

I have to filter the date in load script itself.

any suggestions please

Re: record for single date

ankitaag — Fri, 24 Apr 2015 12:02:21 GMT

Hi,

Please try this

LOAD Date,

Data

FROM

[path\T1.qvd]

(qvd)

where Date(Date,'DD/MM/YYYY')='22/04/2015';

Re: record for single date

Fri, 24 Apr 2015 12:02:48 GMT

HI Ankita,

thanks for the reply.

Its working

Re: record for single date

buzzy996 — Fri, 24 Apr 2015 12:03:38 GMT

just use where condition in ur script load table

where date#(urdatefield,'DD/MM/YYYY')='24/04/2015';

Re: record for single date

rajkumarb — Fri, 24 Apr 2015 12:08:13 GMT

Try Using one of this

where Datefieldname = ''22/04/2015';

where Datefieldname >'21/04/2015' and Datefieldname <'23/04/2015';

where Date(Datefieldname,'DD/MM/YYYY')='22/04/2015';

Re: record for single date

Anonymous — Fri, 24 Apr 2015 12:16:50 GMT

Maybe I"m missing something, but it doesn't work for me. There are two problems:
1. Tables T1 and T2 have the same exact structure. That means that T2 will be autoconcatenated to T1, so all the rows will be in the final table.
2. Date format is 'DD-MM-YYYY' in T1 (and qvd), but 'DD/MM/YYYY' in T2 "where" clause. Nothing will be returned by T2 alone.

The working solution is:

T1:
...

Store T1 into path\T1.qvd (qvd);

T2:
NOCONCATENATE
LOAD Date,
...

where Date#(Date,'DD-MM-YYYY')='22-04-2015';

DROP TABLE T1;

Re: record for single date

ankitaag — Fri, 24 Apr 2015 12:37:39 GMT

Yes Michael,

drop table T1; should be written at the last of the script.

I missed that while copying the script;

Re: record for single date

Anonymous — Fri, 24 Apr 2015 13:18:22 GMT

Speaking of "drop" - it makes sense to drop table right after "store". First, it is removed from memory sooner, and second - no need for noconcatenate.

Small things but helpful when there data is large.