Excel load with Cross Table

[Sai33]

Hello All,

I have a complex Excel File to load in the following format

LOCATION:01 MUC
Dates		Orders			Deliveries
Month	Date	A	B	C	A	B	C
01	01.01.2020	10	20	12	8	13	9

And the Expected Result table is as below

Date	Orders A	Orders B	Orders C	Deliveries A	Deliveries B	Deliveries C
01.01.2020	10	20	12	8	13	9

I'm thinking of using Cross Table functionality as part of the Load Script but wasn't able to figure out to join two Row names. 

Attaching also the Sample Excel Data along with Expected Result.

Thanks and Best Regards

Sai

[Taoufiq_Zarra]

below my proposal with some load script tricks

Data:

LOAD 
     Dates, 
     F2, 
     Orders, 
     F4, 
     F5, 
     Deliveries, 
     F7, 
     F8
FROM
[.\Excel Load.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Sample Data]);

let vFieldNo = NoOfFields('Data'); 

for i=1 to $(vFieldNo)
	LET a =FieldName('$(i)','Data');
	Let b=Peek('$(a)',0,'Data');
		if '$(i)'>2 then
		    if '$(a)'='Orders' then
		    	let c='Orders';
		    elseif '$(a)'='Deliveries' then
		    	let c='Deliveries';
		    end if
	       Rename Field '$(a)' to '$(c) $(b)';
	    else
			Rename Field '$(a)' to '$(b)';
		endif
	
Next i;

output:
noconcatenate
load * resident Data where RecNo()>1;

drop table Data;

output:

[ArnadoSandoval · Accepted solution]

Hi Sai,

The Header in your Excel file is split on rows 2 and 3, but actually row 3 contains all the information you need, for Qlik loaders script, my proposed script is shown below:

Data:
LOAD
    Month,
    Date,
    A             as [Orders A],
    B             as [Orders B],
    C             as [Orders C],
    A1            as [Deliveries A],
    B1            as [Deliveries B],
    C1            as [Deliveries C]
FROM [lib://Excel_Data/Excel Load.xlsx]
(ooxml, embedded labels, header is 2 line, table is [Sample Data]);

The format specification in the load statements describes header is 2 line, e.g. the data's header is on the third row (Qlik's loader counts rows from zero); the third row contains these labels: Month, Data, A, B, C, A, B, C; when Qlik find a repeated column header it add a sequence number to the original label; so the script actually received these header labels: Month, Data, A, B, C, A1, B1, C1; then we just aliased these columns to the required header name as shown in the code. 

Note: header is 2 line is not the same as header is 2 lines.

Hope this helps