topic How to create a date from month and year ? in QlikView

How to create a date from month and year ?

paulyeo11 — Fri, 29 May 2015 13:26:22 GMT

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

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

paulyeo11 — Fri, 29 May 2015 13:27:43 GMT

Hi All

Enclosed my QV Doc

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

paulyeo11 — Fri, 29 May 2015 20:47:41 GMT

Hi All

Can some one help me ?

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

MarcoWedel — Fri, 29 May 2015 22:51:56 GMT

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

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

paulyeo11 — Sat, 30 May 2015 02:02:30 GMT

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.

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

Sat, 30 May 2015 10:20:20 GMT

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 ?

PrashantSangle — Sat, 30 May 2015 10:28:35 GMT

Hi,

You can try Resident load

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

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

paulyeo11 — Sat, 30 May 2015 11:52:55 GMT

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 ?

PrashantSangle — Sat, 30 May 2015 11:59:32 GMT

Welcome..

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

MarcoWedel — Sat, 30 May 2015 23:04:03 GMT

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

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

paulyeo11 — Sun, 31 May 2015 00:55:56 GMT

Hi Sir

Thank you very much.

Sent from my android device.