How to create a date from month and year ?

[paulyeo11]

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

[paulyeo11]

Hi All

Enclosed my QV Doc

[paulyeo11]

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]

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.

[#6]

Hi Paul,

Try like this

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

Thanks

Harsha Nandan

[PrashantSangle · Accepted solution]

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

[paulyeo11]

Hi Sir

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

Sent from my android device.

[PrashantSangle]

Welcome..

[MarcoWedel]

Hi,

another solution could be:

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