topic Re: Data Format Change in QV in QlikView

Data Format Change in QV

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

Hi Guys,

I have a column "Date" from the source as shown in the screenshot below.

But, I need to create another column based on the values from this "Date" column as shown below.

Also, attached the sample dataset. Please help.

Thanks,

Siva.

Re: Data Format Change in QV

isingh30 — Tue, 22 May 2018 14:57:00 GMT

Did you try cross tables?

Thank you!

Re: Data Format Change in QV

Anonymous — Tue, 22 May 2018 15:27:09 GMT

I tried cross tables, but couldn't get the expected results.

Re: Data Format Change in QV

isingh30 — Tue, 22 May 2018 15:29:19 GMT

stalwar1

Sunny bhai please advise.

Thank you!

Re: Data Format Change in QV

vishsaggi — Tue, 22 May 2018 16:17:25 GMT

Check if this is what you are looking for?

MonthMap:

Mapping LOAD MonthName, MonthNo INLINE [

MonthName, MonthNo

Jan, 1

Feb, 2

Mar, 3

Apr, 4

May, 5

Jun, 6

Jul, 7

Aug, 8

Sep, 9

Oct, 10

Nov, 11

Dec, 12

];

Check:

LOAD Date, ApplyMap('MonthMap', Month) AS MonthNo, Year, IF(NOT Len(Day) = 4, Day) AS Day;

LOAD Date,

IF(ISNUM(Date), Date) AS Day,

If(NOT IsNum(Date), Date) AS Month,

IF(Len(Date) = 4, Date) AS Year

FROM

Date.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

MaxField:

LOAD

Max(MonthNo) AS MaxMonth,

Max(Day) AS MaxDay

Resident Check;

LET vDay = Peek('MaxDay');

LET vMonthNo = Peek('MaxMonth');

Drop Table MaxField;

For i= 1 to vMonthNo

For j =1 to vDay

Calendar:

LOAD MakeDate(Year, $(i), $(j)) AS DateField

Resident Check;

Next j

Next i

-- Sorry missed MonthMap table for mapping. But sunny's script is more elegant and i would go with that.

Re: Data Format Change in QV

sunny_talwar — Tue, 22 May 2018 16:19:52 GMT

Try this

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

[..\..\Downloads\Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(New_Date)) > 0;

DROP Table Table;

Re: Data Format Change in QV

Anonymous — Tue, 22 May 2018 18:22:34 GMT

Thank you so much, Sunny. Got it.