Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Generate Dates based on Day names - Crosstable

Hello.

I am looking to convert data into a linear table from crosstable structure and need help. My data looks like Sheet1 and my desired output is Sheet2 in the attached excel. Any help is appreciated.

Thanks.

Sheet1:

Crosstable Sheet1.JPG

Sheet2:

Crosstable Sheet2.JPG

1 Reply
sunny_talwar

May be this

MappingWeekDay:

Mapping

LOAD * Inline [

  WeekDay, WeekDayNum

  Sunday, 0

    Monday, 1

    Tuesday, 2

    Wednesday, 3

    Thursday, 4

    Friday, 5

    Saturday, 6

];

Table:

CrossTable (WeekDay, Value, 5)

LOAD [Week starts on],

    Category,

    Name,

    Task,

    State,

    Sunday,

    Monday,

    Tuesday,

    Wednesday,

    Thursday,

    Friday,

    Saturday

FROM

[..\..\Downloads\Crosstable.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  ApplyMap('MappingWeekDay', WeekDay, Null()) as WeekDayNum,

  Date(RangeSum([Week starts on], ApplyMap('MappingWeekDay', WeekDay, Null()))) as Date

Resident Table

Where Value <> 0;

DROP Table Table;