topic Re: Restrict a data set based on date: What am I doing wrong? in QlikView

Restrict a data set based on date: What am I doing wrong?

josephinetedesc — Wed, 30 Sep 2015 00:09:30 GMT

I think what is happening is:

[date Start Tx] is working ok

BUT

DateFull - which I want because it is related to a Calendar - this is giving me ALL rows in the spreadsheet.

How do I restrict the values of DateFull? It must have something to do with being read into memory before the restriction takes place???

tableUse:

LOAD

[date Start Tx],

[date Start Tx] as DateFull,

location

FROM

[..\spreadsheet files\tableUse.xls]
(biff, embedded labels, table is Sheet1$)

WHERE
[date Start Tx] >= '01/04/2015'
// and [date Start Tx] >= 42095
AND location <> 'East'
;

Message was edited by: Josephine Tedesco I have attached the file, the date is unchanged but I have removed other columns and changed the ids Jo

Re: Restrict a data set based on date: What am I doing wrong?

sunny_talwar — Wed, 30 Sep 2015 01:06:57 GMT

May be this:

tableUse:

LOAD

[date Start Tx],

[date Start Tx] as DateFull,

location

FROM

[..\spreadsheet files\tableUse.xls]
(biff, embedded labels, table is Sheet1$)
WHERE
[date Start Tx] >= Date#('01/04/2015', 'DD/MM/YYYY') //Assuming that your date is in DD/MM/YYYY format
// and [date Start Tx] >= 42095
AND location <> 'East'

Re: Restrict a data set based on date: What am I doing wrong?

josephinetedesc — Wed, 30 Sep 2015 03:48:21 GMT

Hi Sunny

thank you for your help

This ended up working

[date Start Tx]>= 01/04/2015

but the DateFull still gives me all the values in the Calendar (but not thankfully all the values in the spreadsheet) - so that is good,

Re: Restrict a data set based on date: What am I doing wrong?

PrashantSangle — Wed, 30 Sep 2015 04:06:31 GMT

Hi,

Can you share your xls sheet.

Regards

Re: Restrict a data set based on date: What am I doing wrong?

josephinetedesc — Wed, 30 Sep 2015 04:46:49 GMT

Hi Max

I have uploaded the file ... (I think) let me know if you cannot see it.

Re: Restrict a data set based on date: What am I doing wrong?

PrashantSangle — Wed, 30 Sep 2015 04:57:50 GMT

Hi,

Your date format is M/D/YYYY

therefore you try

[date Start Tx] >= date#('4/1/2015','M/D/YYYY')

Regards

Re: Restrict a data set based on date: What am I doing wrong?

josephinetedesc — Wed, 30 Sep 2015 05:05:20 GMT

Thank you this works ☺

How did you know what the format was???

Also – I only see “correct answer sometimes … how should I be opening the files – is the correct sequence:

Re: Restrict a data set based on date: What am I doing wrong?

nagarjuna_kotha — Wed, 30 Sep 2015 05:27:19 GMT

As per your data If you just Put below code also works same:

LOAD location,

URNUM,

[date Start Tx]

FROM

(ooxml, embedded labels, table is Sheet1)Where location<>'East' ;