topic Re: Date generation in QlikView

Date generation

rahulsud007 — Wed, 18 Mar 2015 05:44:01 GMT

ID	jan	rowno()	Year
AO	0	1	2015
AO	1	2	2015
AO	0	3	2015
AO	1	4	2015
AO	1	5	2015

Dear Experts, this is the format which I currently have

and I need the output by replacing Rowno with date. Such as

date

1-jan-2015

2-jan-2015

3-jan-2015

4-jan-2015

5-jan-2015. Can you please help me in this?

Re: Date generation

Wed, 18 Mar 2015 05:49:05 GMT

Hi,

put it like,

rowno()&'-'&Month&'-'&Year as NewDate

Re: Date generation

rahulsud007 — Wed, 18 Mar 2015 05:54:35 GMT

Hi harshal, thanks for your Feedback but the thing is that I am not getting the rowno() as 1,2,3,4,5 as I mentioned it is as such 4021,

4022,4033.. so on

Re: Date generation

Wed, 18 Mar 2015 05:54:42 GMT

Hi,

try something like that and if your jan is fix in field then you replace in makedate by 1

TEST:

LOAD * inline [

ID, jan ,rowno() ,Year

AO ,0 ,1, 2015

AO ,1, 2, 2015

AO, 0 ,3, 2015

AO ,1, 4, 2015

AO ,1, 5, 2015];

TEST2:

LOAD ID,

MakeDate(Year,jan,rowno()) as date

Resident TEST;

Regards

Vimlesh

Re: Date generation

senpradip007 — Wed, 18 Mar 2015 05:54:58 GMT

Do you have any month field?

Re: Date generation

Wed, 18 Mar 2015 05:58:15 GMT

Can you give sample of accessing data

or clarify that how you manage rows with days like 1 ot 31 rows.

Regards

Vimlesh

Re: Date generation

senpradip007 — Wed, 18 Mar 2015 05:59:03 GMT

you can use like

makedate(year, month(rowno()), day(rowno())) as Date

Re: Date generation

Wed, 18 Mar 2015 06:04:40 GMT

Hi,

May be it will match with yur req..

Test:

Load

ID,

Year,

date(Month,'MMM') as NewMonth

from source;

Load *,

rowno() as Key,

rowno() &'-'&NewMonth&'-'&Year as NewDate

resident Test;

Re: Date generation

rahulsud007 — Wed, 18 Mar 2015 06:08:23 GMT

Hi guys, I do have multiple Month fields You can consider from Jan to December of last 10 years and need to generate a date field for it. But the things is all these month fields are in different table so I just gave a prototype of only one table. Also for february if it is a leap Year i should get date till 29, 28 n for month with 30 days ie april, june,september, november and rest for 31 days.

Re: Date generation

Wed, 18 Mar 2015 06:29:51 GMT

Hi Rahul,

Try below script (You can change the values of vDateMin & vDateMax as per your requirement) -

LET vDateMin = Num(MakeDate(2015,1,1));

LET vDateMax = Floor(MonthEnd(Today()));

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

rowno() AS REC,

Date(TempDate) AS CalendarDate,

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Regards,

Nitesh

Re: Date generation

anbu1984 — Wed, 18 Mar 2015 06:40:38 GMT

Temp:

Load * Inline [

ID,jan,rowno,Year

AO,0,4022,2015

AO,1,4023,2015

AO,0,4033,2015

AO,1,4044,2015

AO,1,4065,2015 ];

Load *,Date#(RowNo()&'-'&'Jan-'&Year,'DD-MMM-YYYY') As Dt Resident Temp Order by Year,rowno;

Drop Table Temp;