Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

josephinetedesc
Contributor III

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

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???

Jo

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

1 Solution

Accepted Solutions

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

Hi,

Your date format is M/D/YYYY

therefore you try

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

Regards

Regards,
Prashant Sangle
7 Replies

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

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'

josephinetedesc
Contributor III

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

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,

Jo

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

Hi,

Can you share your xls sheet.

Regards

Regards,
Prashant Sangle
josephinetedesc
Contributor III

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

Hi Max

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

jo

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

Hi,

Your date format is M/D/YYYY

therefore you try

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

Regards

Regards,
Prashant Sangle
josephinetedesc
Contributor III

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

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:

Log in – find my inbox – then hit reply (and actions such as select “correct answer” will be available?

Jo

nagarjuna_kotha
Valued Contributor II

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

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' ;

Community Browser