Data Format Change in QV

[Anonymous]

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.

[isingh30]

Did you try cross tables?

Thank you!

[Anonymous]

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

[isingh30]

stalwar1

Sunny bhai please advise.

Thank you!

[vishsaggi]

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.

[sunny_talwar · Accepted solution]

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;

[Anonymous]

Thank you so much, Sunny. Got it.