Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
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
Master II
Master II

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;

View solution in original post

6 Replies
benrig44
Contributor III
Contributor III

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
Master II
Master II

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;

View solution in original post

MK9885
Master II
Master II

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
Partner
Partner
Author

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
Partner
Partner
Author

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
Partner
Partner
Author

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