Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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
Highlighted
Partner
Partner

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;

View solution in original post

4 Replies
Highlighted
Partner
Partner

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;

View solution in original post

Highlighted
Contributor III
Contributor III

Re: CrossTable and dates

Thank you very much

Highlighted
Contributor II
Contributor II

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

Highlighted
Creator
Creator

Re: CrossTable and dates

I have a similar requirement where date format is Jun-19, jul-19

But i am not able to see the data after loading.

Please help me with this issue