Loading data with filter on date

[#1]

Hi,

I am new to QLikView. I need to load data from a txt file and put a condition 'where' on the date. The field INV_DATE is in format 2-7-2010 0:00:00. So when I try to edit script after load code it doesnt give me desired  results.

WHERE

INV_DATE > '01-01-2009'

WHERE

INV_DATE > '01/01/2009'

WHERE
INV_DATE > '01-01-2009 0:00:00'

I have tried all possible combinations but I am not able to get results. Please help.

Abhishek

[jagannalla]

Use this code:

Where Date(INV_DATE,'DD-MM-YYYY') > '01-01-2009'

Where Date(INV_DATE,'D-M-YYYY') > '1-1-2009'

Hope it helps you

Cheers!

Jagan

[#3]

Thanks Jagan for your reply.

But this is giving me no results. It is urgent, will appreciate if you can help me.

Abhishek

[jagannalla]

Ok please provide sample data you have.

[#5]

This is how the date column looks like:

1-1-2010 0:00:00

1-1-2011 0:00:00

1-1-2012 0:00:00

1-2-2009 0:00:00

1-2-2010 0:00:00

1-2-2011 0:00:00

1-2-2012 0:00:00

1-3-2009 0:00:00

[jagannalla]

Hello,

It is working perfectly for me. If dates are in this format M-D-YYYY h:mm:ss.

Temp:

LOAD * INLINE [

    INV_DATE

    1-1-2010 0:00:00

    1-1-2011 0:00:00

    1-1-2012 0:00:00

    1-2-2009 0:00:00

    1-2-2010 0:00:00

    1-2-2011 0:00:00

    1-2-2012 0:00:00

    1-3-2009 0:00:00

];

Res:

LOAD Month(INV_DATE) as M,*

Resident Temp

Where Date(INV_DATE,'M-D-YYYY') > '1-2-2009'

;

DROP Table Temp;

- According to your condition 1-1-2009, It will retrive the entire data what we have know. B'coz all dates are greater than this date. If you want to go with preceding load concept, use below code.

Temp:

LOAD Month(INV_DATE) as M,*

Where Date(INV_DATE,'M-D-YYYY') > '1-2-2009'

;

LOAD * INLINE [

    INV_DATE

    1-1-2010 0:00:00

    1-1-2011 0:00:00

    1-1-2012 0:00:00

    1-2-2009 0:00:00

    1-2-2010 0:00:00

    1-2-2011 0:00:00

    1-2-2012 0:00:00

    1-3-2009 0:00:00

];

Please check and let me know if you need any help. Otherwise please attach sample qvw file.

Cheers!!

Jagan

[#7]

Sorry Jagan but it is not working. I am attaching excel file where I ve illustrated the scenario.

Thanks once again, Abhishek

[#8]

With this code :

Where

Date(INV_DATE,'M-D-YYYY') > '1-2-2010';

it is giving me no results. Please note what ive sent you is just a sample of data. I am working on millions of rows.

[jagannalla]

Hello Abhi,

What is the wrong with your data i don't know, are you loading the data form excel or from any other Data source.If i load the excel file with this below code. I can able to retrive the output what you need. For your reference i'm attaching my qvw file. Please check and let me know if I'm wrong. Otherwise please attach sample qvw file, which you are loading from original datasource.

LOAD CUSTOMER_NAME, 
     CONTRACTOR_NAME, 
     INV_DATE
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1)
Where Date(INV_DATE,'M-D-YYYY hh:mm:ss') > '1-1-2010 00:00:00';

Cheers!

Jagan

[#10]

I see ur qlikview file and is workign fine. For me it is not. I am uploading data from txt file. is it a problem?

Also, I will not be able to share qvw file as it is sensitive data, sorry.

Thanks, Abhishek