Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lukasrocek21
New Contributor III

CrossTable and dates

Hi all,

I have a problem with crossTable Load. I have this table in Excel:

diskuze.PNG

I use this code:

Capacity:

CrossTable (Months,Utilization)

LOAD

    Name as WorkerName,

    "43009",

    "43040",

    "43070",

FROM [path.xlsx]

(ooxml,embedded labels,  table is Sheet1);


I need to view dates, but when I try to convert it to date, no results (Date, Timestamp...)

How to load this data? Especially for a heatmap?

Thaks a much for your ideas

1 Solution

Accepted Solutions
shraddha_g
Honored Contributor III

Re: CrossTable and dates

Try:

Capacity:

CrossTable (Months,Utilization,1)

LOAD

    Name as WorkerName,

    "43009",

    "43040",

    "43070",

FROM [path.xlsx]

(ooxml,embedded labels,  table is Sheet1);

Final:

Load WorkerName,

          Utilisation,

date(num#(purgeChar(Months,'"'),'00000')) as Months

Resident Capacity;

Drop table Capacity;

3 Replies
shraddha_g
Honored Contributor III

Re: CrossTable and dates

Try:

Capacity:

CrossTable (Months,Utilization,1)

LOAD

    Name as WorkerName,

    "43009",

    "43040",

    "43070",

FROM [path.xlsx]

(ooxml,embedded labels,  table is Sheet1);

Final:

Load WorkerName,

          Utilisation,

date(num#(purgeChar(Months,'"'),'00000')) as Months

Resident Capacity;

Drop table Capacity;

lukasrocek21
New Contributor III

Re: CrossTable and dates

Thank you very much

mboreham
New Contributor

Re: CrossTable and dates

Thank you so much for this. I will never get the last four hours of my life back but I won't waste the next four. This is wonderful

Community Browser