Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

How to create a date from month and year ?

Hi All

Now i am using below script for date field :-

makedate(@53:57T,@50:52T,1) as date,

Which is wrong , so i change the above field name to date_ ( insted of date )

makedate(@53:57T,@50:52T,1) as date_,

Now i need change to using below 2 field to form the above date field :-

@50:52T  as month_,

@53:57T  as year_,

Now my question is , what is the expression should be , so that above 2 field will be able to create field name same sa date field above ?

For the day i can fill with 1.

Paul

1 Solution

Accepted Solutions

Re: How to create a date from month and year ?

Hi,

You can try Resident load

Like

Temp:

Load

....

//Existing Script

@50:52T  as month_,

@53:57T  as year_,

..

from tableName;

Final:

Load *,

makeDate(year_,month_,1) as NewDate,

Resident Temp;

Drop table Temp;     //If you dont drop it then it will create Synthetic Key with Temp  and Final table

regards

Great dreamer's dreams never fulfilled, they are always transcended.
10 Replies
paulyeo11
Valued Contributor II

Re: How to create a date from month and year ?

Hi All

Enclosed my QV Doc

paulyeo11
Valued Contributor II

Re: How to create a date from month and year ?

Hi All

Can some one help me ?

Re: How to create a date from month and year ?

can you post some sample rows of your file FS_TDSTECH.txt?

makedate(@53:57T,@50:52T,1) as date,


could work depending from the source data.


regards


Marco

paulyeo11
Valued Contributor II

Re: How to create a date from month and year ?

Enclosed the sample of my text file. File name not same but it is ok.

Thank for yr script. I will test later.

Sent from my android device.

Not applicable

Re: How to create a date from month and year ?

Hi Paul,

Try like this

makedate([@53:57T],[@50:52T],1) as date



Thanks

Harsha Nandan

Re: How to create a date from month and year ?

Hi,

You can try Resident load

Like

Temp:

Load

....

//Existing Script

@50:52T  as month_,

@53:57T  as year_,

..

from tableName;

Final:

Load *,

makeDate(year_,month_,1) as NewDate,

Resident Temp;

Drop table Temp;     //If you dont drop it then it will create Synthetic Key with Temp  and Final table

regards

Great dreamer's dreams never fulfilled, they are always transcended.
paulyeo11
Valued Contributor II

Re: How to create a date from month and year ?

Hi Sir

Thank you very much I try to avoid partial reload unless I have no choice.

Sent from my android device.

Re: How to create a date from month and year ?

Welcome..

Great dreamer's dreams never fulfilled, they are always transcended.

Re: How to create a date from month and year ?

Hi,

another solution could be:

QlikCommunity_Thread_166112_Pic2.JPG

QlikCommunity_Thread_166112_Pic1.JPG

LOAD *,

    year(date) as year,

    Month(date) as month,

    year(date2) as year2,

    Month(date2) as month2

Where IsNum(field1);

LOAD [@1:16] as field1,

    [@17:47] as field2,

    [@48:48] as field3,

    Date(Date#([@50:56],'MM YYYY'),'MM/YY') as date,

    Date(Date#([@57:67],'DD/MM/YYYY')) as date2,

    [@68:85] as field4,

    [@86:101] as field5,

    [@103:118] as field6,

    [@119:n] as field7

FROM [https://community.qlik.com/servlet/JiveServlet/download/786306-167361/FS_TID.TXT] (fix, codepage is 1252);

hope this helps

regards

Marco

Community Browser