Excel Data transposition

[alexis]

Hi

I have an excel spreadsheet with columns:

Category
Class
Accounts (one or more)

(see screen shot below and attached spreadsheet TestData.xlsx

I want to read this and create a Qlik table that will have columns

Category  Class  Account
Cat1           I            700160
Cat2            I            700010
Cat2            I            700020
Cat2            I            700030
etc...

Tried CrossTable but failed miserably! 

Any suggestions

Alexis

[Anonymous]

look into SUBFIELD

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunc...

Specifically see paragraph below and example that generates output with headers 

Instrument Player Project

If you use the Subfield() function in a LOAD statement with the optional field_no parameter left out, one full record will be generated for each substring. If several fields are loaded using Subfield() the Cartesian products of all combinations are created.

[Frank_Hartmann · Accepted solution]

try this:

tmp1:
CrossTable(Header, Data, 2)
LOAD *
FROM
[C:\Users\admin\Desktop\TestData (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate

tmp2:
Load * Resident tmp1  where  Data<>Null();
DROP Table tmp1;

[MK9885]

Maybe try
CrossTable (Dims,ACT,3)
LOAD Category,
Class,
Account as ACCT,
Account,
D,
E,
F,
G,
H,
I,
J,
K,
L,
M,
N,
O,
P,
Q,
R,
S
FROM
[C:\.....\TestData.xlsx]
(ooxml, embedded labels, table is Sheet1);

[alexis]

Thanks Franky - I tried something similar but never got there.

So simple when you know how - the second part of the code seems unnecessary making this a 4-line solution code!

Much obliged

Alexis

[alexis]

Hi Benrig

Thanks for the suggestion - I am not sure if the SUBFIELD suggestion would work in this instance as the data is not delimited - each account number is in a separate cell/column in Excel

Appreciate the effort and suggestion

Alexis

[alexis]

Hi Khan_Mohammed

Your solution worked initially but then I added another account on row 3 (Cat2) and your code did not pick it up. The point I am making is that your solution requires the hard-coding of the maximum number of columns which is impractical and unusable.

Thank you for taking the time to respond.

Alexis