topic Re: Forming a Date column in QlikView

Forming a Date column

PrakashMathi — Wed, 25 Nov 2020 16:16:04 GMT

I want to form a date column for a calendar year.

2018

Jan

Feb

Dec

2019

Jan

etc

I want to form a date using Dimension 1 column.

Expected output = 2018-1-30

Attached data set

Message was edited by: prakash mathiazhagan

Re: Forming a Date column

Anil_Babu_Samineni — Tue, 22 May 2018 02:48:27 GMT

Not sure, I understand the problem. Can you bell on that exactly what you need?

Re: Forming a Date column

shiveshsingh — Tue, 22 May 2018 03:16:58 GMT

Do you need a calendar?

Re: Forming a Date column

PrakashMathi — Tue, 22 May 2018 11:24:56 GMT

I want a date column

Re: Forming a Date column

PrakashMathi — Tue, 22 May 2018 11:29:37 GMT

I want a date column in the presentation layer in the format of 2018-1-1, 2018-1-2,etc

Re: Forming a Date column

shiveshsingh — Tue, 22 May 2018 13:02:34 GMT

May be this

let vMin = num(Today());

let vMax = num(Today()+30);

Calendar:

load date($(vMin)+RowNo()-1) as Date

AutoGenerate($(vMax)-$(vMin));

Re: Forming a Date column

jonathandienst — Tue, 22 May 2018 13:24:53 GMT

How should he table containing the date column be associated with the rest of the data model?

Re: Forming a Date column

PrakashMathi — Tue, 22 May 2018 16:54:36 GMT

it is the single table. There is not any other table in data model

Re: Forming a Date column

PrakashMathi — Tue, 22 May 2018 18:04:38 GMT

Actually i was thinking of creating another column which will have Jan/Feb/Mar like that and then to concatenate.

Re: Forming a Date column

PrakashMathi — Tue, 22 May 2018 18:23:09 GMT

All,

Thanks for your help.

Got the answer. Below code works and provides answer to my solution.

Table:
LOAD Date,
If(Date > 2000 and IsNum(Date), Date, Peek('Year')) as Year,
If(IsText(Date), Date, Peek('Month')) as Month,
MakeDate(If(Date > 2000 and IsNum(Date), Date, Peek('Year')), Month(Date#(If(IsText(Date), Date, Peek('Month')), 'MMM')), Date) as New_Date
FROM

(ooxml, embedded labels, table is Sheet1);

FinalTable:
NoConcatenate
LOAD *
Resident Table
Where Len(Trim(New_Date)) > 0;

DROP Table Table;

EXIT SCRIPT;