topic Re: Add date condition in excel export in QlikView

Add date condition in excel export

anuradhaa — Thu, 14 Jan 2016 09:21:33 GMT

I have a CSV file and it has date format as 'd-mmm-yy' (9-Jan-11 etc). I want to use date in where clause when export this data to qlikview.

say i want date > '1-Jan-2016' how can i do that.

Re: Add date condition in excel export

jagan — Thu, 14 Jan 2016 09:25:21 GMT

Hi,

It is not possible to limit the data while exporting to Excel, it just exports whatever data is available in the chart. To overcome this create a button and apply the file and then export using macro.

Useful Qlikview Macros

Regards,

jagan.

Re: Add date condition in excel export

PrashantSangle — Thu, 14 Jan 2016 09:25:49 GMT

Hi,

Load *

from file

where date(dateField) > date(date#('1-Jan-2016'))

Regards

Re: Add date condition in excel export

manojkulkarni — Thu, 14 Jan 2016 09:25:58 GMT

something like

Load *

From test.csv

Where Date(DateField,'DD-MMM-YYYY') > '01-Jan-2016' ;

Re: Add date condition in excel export

tresB — Thu, 14 Jan 2016 09:27:03 GMT

I guess you meant import . If so, you can try like:

...

Where Date(Date#(DateField, 'DD-MMM-YY') , 'DD-MMM-YYYY') > '1-Jan-2016' ;

Re: Add date condition in excel export

anuradhaa — Thu, 14 Jan 2016 11:37:43 GMT

what happens if date is like 'Mar 27, 2015' and it's as general

Re: Add date condition in excel export

PrashantSangle — Thu, 14 Jan 2016 11:46:16 GMT

Hi,

QlikView allowed you to format date as per requirement .

For above format you can try

Date(DateField,'MMM DD,YYYY')

For details you can read blog written by Henric.

The Date Function

Regards

Re: Add date condition in excel export

anuradhaa — Mon, 18 Jan 2016 15:19:46 GMT

see attached csv.

Here i want to import data which has "Accepted at" in 2011.

Thanks

Re: Add date condition in excel export

sunny_talwar — Mon, 18 Jan 2016 15:34:00 GMT

May be this:

SET DateFormat='D-MMM-YY';

Table:

LOAD Id,

[Created at],

[Accepted at]

FROM

testcsv.xlsx

(ooxml, embedded labels, table is Sheet1)

Where [Accepted at] > MakeDate(2011, 1, 1);

Re: Add date condition in excel export

jagan — Tue, 19 Jan 2016 00:30:21 GMT

Hi,

If you just want data which is Accepted In 2011 then use

Data:

LOAD Id,

Date([Created at], 'MM/DD/YYYY') AS [Created at],

Date([Accepted at], 'MM/DD/YYYY') AS [Accepted at]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE Year(Date([Accepted at], 'MM/DD/YYYY')) = 2011;

Hope this helps you.

Regards,

jagan.