Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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
PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

10 Replies
paulyeo11
Master
Master
Author

Hi All

Enclosed my QV Doc

paulyeo11
Master
Master
Author

Hi All

Can some one help me ?

MarcoWedel

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
Master
Master
Author

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

Hi Paul,

Try like this

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



Thanks

Harsha Nandan

PrashantSangle

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.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
paulyeo11
Master
Master
Author

Hi Sir

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

Sent from my android device.

PrashantSangle

Welcome..

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
MarcoWedel

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