Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading data with filter on date

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

1 Solution

Accepted Solutions
jagan
MVP
MVP

Hi,

Try this script

Directory;

LOAD CUSTOMER_NAME,

     INV_DATE

FROM

Temptxt.txt

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where Date(Date#(INV_DATE,'M-D-YYYY hh:mm:ss')) > Date(Date#('1-1-2010 00:00:00', 'M-D-YYYY hh:mm:ss'))

;

Hope it helps you.

Regards,

Jagan.

View solution in original post

17 Replies
jagannalla
Partner
Partner

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

Not applicable
Author

Thanks Jagan for your reply.

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

Abhishek

jagannalla
Partner
Partner

Ok please provide sample data you have.

Not applicable
Author

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

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

Not applicable
Author

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

Thanks once again, Abhishek

Not applicable
Author

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

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

Not applicable
Author

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