What is the best way to read .csv or .xlsx with this structure?

[nenadvukovic]

Greetings,

Can anybody offer a nice, easy and fast way to read into QlikView an Excel/csv file with the structure as in the attached file? Note that in reality there are hundreds of lines and hundreds of columns.

Thank you

[rubenmarin]

Hi, I think that crosstable will help loading this data:

CrossTable(data_type, Value, 2)
LOAD 
     *
FROM
[[Path_to_file]\S4-07-05-2020.xlsx]
(ooxml, embedded labels, table is Sheet1);

[nenadvukovic]

Ruben, thanks for looking into this.

I've missed explaining what result is expected. Sorry about that. This is what I get with your script:

I need this (ignore index):

timestamp, machineID (left(data_type, 4)), ItemID, Target, Count

2020-05-07 01:00:00, S4, ID1, 747, 715

etc.

[rubenmarin · Accepted solution]

Hi, I haven't tested but it can be something like:

Crosstable:
CrossTable(data_type, Value, 2)
LOAD 
     *
FROM
[[Path_to_file]\S4-07-05-2020.xlsx]
(ooxml, embedded labels, table is Sheet1);

GroupType:
LOAD
	index,
	timestamp,
	Left(GroupData,2) as MachineID,
	MaxString(ItemID) as ItemID,
	MaxString(Target) as Target,
	Max(Count) as Count
Group By index, timestamp, GroupData;
LOAD
	index, 
	timestamp, 
	SubField(data_type, '_', 1) as GroupData,
	If(SubField(data_type, '_', 2)='code', Value) as ItemID, 
	If(SubField(data_type, '_', 2)='target', Value) as Target, 
	If(SubField(data_type, '_', 2)='cnt1', Value) as Count
Resident Crosstable;

DROP Table Crosstable;

[nenadvukovic]

Works from the start. Fantastic! Thanks a lot Ruben.

I haven't used the MaxString() function so far. Cool!