Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
Partner
Partner

Excel Data transposition

Hi

I have an excel spreadsheet with columns:

Category
Class
Accounts (one or more)

(see screen shot below and attached spreadsheet TestData.xlsx

 

Test.jpg

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

 



 

Labels (2)
1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: Excel Data transposition

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;
6 Replies
benrig44
New Contributor III

Re: Excel Data transposition

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
Honored Contributor II

Re: Excel Data transposition

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;
Khan_Mohammed
Honored Contributor II

Re: Excel Data transposition

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

Re: Excel Data transposition

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

 

 

Partner
Partner

Re: Excel Data transposition

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

 

 

Partner
Partner

Re: Excel Data transposition

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