Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
josephinetedesc
Creator III
Creator 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
PrashantSangle

Hi,

Your date format is M/D/YYYY

therefore you try

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

7 Replies
sunny_talwar

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
Creator III
Creator III
Author

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

PrashantSangle

Hi,

Can you share your xls sheet.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
josephinetedesc
Creator III
Creator III
Author

Hi Max

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

jo

PrashantSangle

Hi,

Your date format is M/D/YYYY

therefore you try

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
josephinetedesc
Creator III
Creator III
Author

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
Partner - Specialist II
Partner - Specialist II

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