Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
kasimyc
Contributor II
Contributor II

I need to convert my example table to a crosstable

hi all,

I want to make a crosstab of the dates in the sample file. how can i achieve this?

So I want to collect the dates under the "Days" column name under the day information.

any help, would be appreciated! thanx

 

kasimyc_0-1605091055665.png

 

Labels (1)
1 Solution

Accepted Solutions
QFabian
Specialist II
Specialist II

Hi, first all, do you know this wizard : ?

QFabian_0-1605098103757.png

it works when you load a excel file, and try with the next button two times :

QFabian_1-1605098157500.png

then tyou have to specify the values  :

QFabian_2-1605098242441.png

then the crosstable is done :

QFabian_3-1605098291808.png

it left this script :

 

Aux:
CrossTable(Date, Value, 9)
LOAD ID,
ReGID,
Name,
Statu,
Task,
Location,
SubTask,
[Full/Partitime],
workdate,
[1.07.2020],
[2.07.2020],
[3.07.2020],
[4.07.2020],
[5.07.2020]
FROM
[..\Documents\CrossBit\Marketing\crosstable.xlsx]
(ooxml, embedded labels, table is Hoja1);

 

I called the table 'Aux', because the format is not valid for the dates, so we have to do one more thing :


Data:
Load
*,
makedate(subfield(Date, '.', 3),subfield(Date, '.', 2),subfield(Date, '.', 1)) as ValidDate
Resident Aux;
drop table Aux;

 

QFabian

View solution in original post

3 Replies
PriyankaShivhare
Creator II
Creator II

Something like below

crosstable(Day,Dayinformation,9)

Load *

from source;

 

thanks,

priyanka

MayilVahanan

Hi @kasimyc 

Try like below

CrossTable(Day, Value, 9)
LOAD *
FROM
[aa.xlsx]
(ooxml, embedded labels, table is Sayfa1);

 

Thanks & Regards,
Mayil Vahanan R
QFabian
Specialist II
Specialist II

Hi, first all, do you know this wizard : ?

QFabian_0-1605098103757.png

it works when you load a excel file, and try with the next button two times :

QFabian_1-1605098157500.png

then tyou have to specify the values  :

QFabian_2-1605098242441.png

then the crosstable is done :

QFabian_3-1605098291808.png

it left this script :

 

Aux:
CrossTable(Date, Value, 9)
LOAD ID,
ReGID,
Name,
Statu,
Task,
Location,
SubTask,
[Full/Partitime],
workdate,
[1.07.2020],
[2.07.2020],
[3.07.2020],
[4.07.2020],
[5.07.2020]
FROM
[..\Documents\CrossBit\Marketing\crosstable.xlsx]
(ooxml, embedded labels, table is Hoja1);

 

I called the table 'Aux', because the format is not valid for the dates, so we have to do one more thing :


Data:
Load
*,
makedate(subfield(Date, '.', 3),subfield(Date, '.', 2),subfield(Date, '.', 1)) as ValidDate
Resident Aux;
drop table Aux;

 

QFabian

View solution in original post