topic Re: Appending Date in QlikView

Appending Date

mansi_dv — Fri, 21 Mar 2014 12:10:49 GMT

I have an excel document from which I load my data. But the excel sheet has contents as below:

Document Name:

Execution Date:

Execution Time:

etcetra

and then I have the actual data wihich I want to load.

Now, I remove all the above data in the [File Wizard: Type] and keep only the table. But the issue is, I want to append the year, Quarter and month from the above mentioned date in my script. Is it possible?

Re: Appending Date

jsingh71 — Fri, 21 Mar 2014 12:13:33 GMT

Can You share some sample data?

Re: Appending Date

MK_QSL — Fri, 21 Mar 2014 12:13:40 GMT

Can you load your sample file?

Re: Appending Date

Fri, 21 Mar 2014 12:15:33 GMT

Hi,

Can you post the excel file with sample data..

Re: Appending Date

sundarakumar — Fri, 21 Mar 2014 12:18:25 GMT

Try adding

month(execution date) as month,

Q&ceil(month(execution date)/3) as quarter

To the code. this may help.. pls post sample..

Re: Appending Date

Fri, 21 Mar 2014 12:18:40 GMT

You could load the full file and then split the Data with resident load.

Example:

FULL_DATA:

LOAD

FROM ...

;

META_DATA:

LOAD

B as DocumentName

Resident FULL_DATA

where A = 'Document Name:'

;

DATA

LOAD

A as ColumnA

Resident FULL_DATA

where A <> 'Document Name:'

Re: Appending Date

its_anandrjs — Fri, 21 Mar 2014 12:22:11 GMT

Hi,

Please share any date if possible other wise do this as you say you want to load year, Quarter and month from above table so do some thing like

Load

Yourdate_Field,

Year(Yourdate_Field) as Year,

Month(Yourdate_Field) as Month,

'Q'&Ceil(Yourdate_Field/3) as Quarter

from Table;

and link this table with above table not need to join.

Hope this helps

Thanks & Regards

Re: Appending Date

its_anandrjs — Fri, 21 Mar 2014 12:23:35 GMT

Hi,

You can do this in your existing table also add this code only

Yourdate_Field,

Year(Yourdate_Field) as Year,

Month(Yourdate_Field) as Month,

'Q'&Ceil(Yourdate_Field/3) as Quarter

Hope this helps

Thanks & Regards

Re: Appending Date

mansi_dv — Fri, 21 Mar 2014 12:27:50 GMT

The Sample file is attached.

Re: Appending Date

mansi_dv — Fri, 21 Mar 2014 12:30:10 GMT

I hope the issue is understood correctly..

Re: Appending Date

sundarakumar — Fri, 21 Mar 2014 12:36:02 GMT

U should have a date field anywhere in the data, but u have specified an inerval for the data...

Does all the data shown here fall under the july2012 to aug 2012.?

-Sundar

Re: Re: Appending Date

Fri, 21 Mar 2014 12:47:30 GMT

I build an example how I understand your issue.

Re: Appending Date

mansi_dv — Fri, 21 Mar 2014 12:47:48 GMT

Ya but I need the aug 2012 part...ie I want to append 'august' as month, '2012' as year and 'Q2' as quarter.

Re: Appending Date

sundarakumar — Fri, 21 Mar 2014 12:53:26 GMT

If all the data are going to ahve aug 2012 and Q2 then u can do it as

load,

field1,

2012 as year,

'Aug' as month,

'Q2' as quarter

from

table.xls;

-Sundar

Re: Appending Date

mansi_dv — Fri, 21 Mar 2014 13:01:05 GMT

Ya I have done this only Sundarakumar, but went the app will be given to the client, more and more monthly data will be added...all such data should be dynamically appended in the files. So I need a method to do that.

Re: Appending Date

sundarakumar — Fri, 21 Mar 2014 13:04:01 GMT

What do u expect the data source to be? excel fiels? how often will u get them?

Will u get it in a certain interval of time?

will the files be in the same format or will it have the same fields?

-Sundar

Re: Appending Date

mansi_dv — Mon, 24 Mar 2014 04:04:50 GMT

ya data source will be excel files and can be given any time so I need to append date from the period part.

All the files will be in the similar format.

Re: Appending Date

Mon, 24 Mar 2014 06:14:35 GMT

Hi,

Check the attached one