I need to convert my example table to a crosstable

[kasimyc]

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

[PriyankaShivhare]

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);

[QFabian · Accepted solution]

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

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

then tyou have to specify the values  :

then the crosstable is done :

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;