topic Re: Excel Data transposition in App Development

Excel Data transposition

alexis — Sat, 16 Nov 2024 21:51:59 GMT

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

Re: Excel Data transposition

Anonymous — Mon, 19 Nov 2018 17:41:42 GMT

look into SUBFIELD

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

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.

Re: Excel Data transposition

Frank_Hartmann — Mon, 19 Nov 2018 18:29:29 GMT

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;

Re: Excel Data transposition

MK9885 — Mon, 19 Nov 2018 18:52:27 GMT

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

Re: Excel Data transposition

alexis — Mon, 19 Nov 2018 21:00:02 GMT

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

Re: Excel Data transposition

alexis — Mon, 19 Nov 2018 21:02:08 GMT

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

Re: Excel Data transposition

alexis — Mon, 19 Nov 2018 21:10:11 GMT

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