topic Re: Loading data with filter on date in QlikView

Loading data with filter on date

Thu, 30 Aug 2012 07:32:48 GMT

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

Re: Loading data with filter on date

jagannalla — Thu, 30 Aug 2012 08:05:51 GMT

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

Re: Loading data with filter on date

Thu, 30 Aug 2012 08:28:56 GMT

Thanks Jagan for your reply.

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

Abhishek

Re: Loading data with filter on date

jagannalla — Thu, 30 Aug 2012 08:37:48 GMT

Ok please provide sample data you have.

Re: Loading data with filter on date

Thu, 30 Aug 2012 08:39:26 GMT

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

Re: Loading data with filter on date

jagannalla — Thu, 30 Aug 2012 09:33:46 GMT

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

Re: Loading data with filter on date

Thu, 30 Aug 2012 10:10:56 GMT

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

Thanks once again, Abhishek

Re: Loading data with filter on date

Thu, 30 Aug 2012 10:22:44 GMT

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.

Re: Loading data with filter on date

jagannalla — Thu, 30 Aug 2012 10:35:52 GMT

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

Re: Loading data with filter on date

Thu, 30 Aug 2012 10:42:04 GMT

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

Re: Loading data with filter on date

jagannalla — Thu, 30 Aug 2012 11:31:42 GMT

Ok tell me from which data source ur getting this data. I mean either from sql or txt or excel or qvd file etc.

Re: Loading data with filter on date

Thu, 30 Aug 2012 15:26:12 GMT

txt file

Re: Loading data with filter on date

jagannalla — Thu, 30 Aug 2012 15:49:20 GMT

Hello,

I've taken txt file and it is working fine for me. Please check txt file and qvw file once again.

Re: Loading data with filter on date

Fri, 31 Aug 2012 05:31:19 GMT

Weird it is.

You know I took your text file. Created a new qlikview file and loaded text file. and the codes do not work for me.

Attaching files for your reference.

thanks for all your efforts, Abhishek

Re: Loading data with filter on date

jagan — Fri, 31 Aug 2012 07:23:21 GMT

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.

Re: Loading data with filter on date

jagannalla — Fri, 31 Aug 2012 08:43:17 GMT

Use this code:

Directory;
LOAD CUSTOMER_NAME,
INV_DATE
FROM
Temptxt.txt
(txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where Timestamp(Timestamp#(INV_DATE,'MM-DD-YYYY hh:mm:ss')) > Timestamp(Timestamp#('1-1-2010 00:00:00', 'M-D-YYYY hh:mm:ss'))
;

- If you attach sample file with sample data what you have, it helps others to solve the problem quickly. It saves ur's and others time also.

Cheers!!

Jagan

Re: Loading data with filter on date

Fri, 31 Aug 2012 09:14:05 GMT

Thanks a lott Jagan. It works!!!!!

and I apologise for takign up your time. I will keep in mind in future to load files.

Thanks a ton, Abhishek

Re: Loading data with filter on date

Wed, 12 Sep 2012 14:02:13 GMT

Hi Jagan,

I have loaded the data succesfully. What I need to do as a next step, give user an option on the tool to give a date range and see the output for that date range. The date range will be in DDMMYYYY format.

How to do this.

Please help, Abhishek