Generate Dates based on Day names - Crosstable

Fri, 16 Jun 2017 18:22:11 GMT

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:

Sheet2:

Re: Generate Dates based on Day names - Crosstable

sunny_talwar — Fri, 16 Jun 2017 18:35:38 GMT

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;

topic Re: Generate Dates based on Day names - Crosstable in QlikView

Generate Dates based on Day names - Crosstable

Re: Generate Dates based on Day names - Crosstable