Convert columns to rows

[PaulK]

Hi

I'm new to Qlik Sense and I was wondering if it is possible to load and excel file and then display values from some of the columns into rows in a straight table?

So the original file has the following fields, quantities and prices in separate columns for each chemical (up to 10 different quantities and price for each chemical). I've only shown the first 4 chemicals in the list, the original excel file has 107800 chemicals listed and some chemical have more quantities and prices than the example.

SMILES

Chemical_Name

Catalog_Number

CAS_Number

Inventory

Purity

Leadtime

Quantity1

Prices1

Quantity2

Prices2

Quantity3

Prices3

Quantity4

Prices4

Quantity5

Prices5

Quantity6

Prices6

Quantity7

Prices7

Quantity8

Prices8

Quantity9

Prices9

Quantity10

Prices10

OC(=O)c1ccc(cc1)C(=O)O

1,4-Benzenedicarboxylic acid

AG00005N

100-21-0

14500g

99%

5 days

25g

4

100g

5

500g

7

CC(=O)c1ccc(cc1)[N+](=O)[O-]

Ethanone, 1-(4-nitrophenyl)-

AG00005P

100-19-6

8150g

97%

5 days

25g

5

100g

8

300g

22

500g

23

1000g

44

CC(c1ccc(cc1)C(C)C)C

Benzene, 1,4-bis(1-methylethyl)-

AG00005Q

100-18-5

23975g

98%

5 days

5g

10

10g

12

25g

15

CNc1ccc(cc1)[N+](=O)[O-]

Benzenamine, N-methyl-4-nitro-

AG00005T

100-15-2

3450g

97%

5 days

5g

6

10g

7

25g

9

and the table in Qlik Sense displays the quantity and price on separate rows for each chemical, I also need the Quantities to be uppercase and not lower case as they are in the original file

SMILES	Chemical_Name	Catalog_Number	CAS_Number	Inventory	Purity	Leadtime	Quantity	Price
OC(=O)c1ccc(cc1)C(=O)O	1,4-Benzenedicarboxylic acid	AG00005N	100-21-0	14500g	99%	5 days	25G	4
OC(=O)c1ccc(cc1)C(=O)O	1,4-Benzenedicarboxylic acid	AG00005N	100-21-0	14500g	99%	5 days	100G	5
OC(=O)c1ccc(cc1)C(=O)O	1,4-Benzenedicarboxylic acid	AG00005N	100-21-0	14500g	99%	5 days	500G	7
CC(=O)c1ccc(cc1)[N+](=O)[O-]	Ethanone, 1-(4-nitrophenyl)-	AG00005P	100-19-6	8150g	97%	5 days	25G	5
CC(=O)c1ccc(cc1)[N+](=O)[O-]	Ethanone, 1-(4-nitrophenyl)-	AG00005P	100-19-6	8150g	97%	5 days	100G	8
CC(=O)c1ccc(cc1)[N+](=O)[O-]	Ethanone, 1-(4-nitrophenyl)-	AG00005P	100-19-6	8150g	97%	5 days	300G	22
CC(=O)c1ccc(cc1)[N+](=O)[O-]	Ethanone, 1-(4-nitrophenyl)-	AG00005P	100-19-6	8150g	97%	5 days	500G	23
CC(=O)c1ccc(cc1)[N+](=O)[O-]	Ethanone, 1-(4-nitrophenyl)-	AG00005P	100-19-6	8150g	97%	5 days	1000G	44
CC(c1ccc(cc1)C(C)C)C	Benzene, 1,4-bis(1-methylethyl)-	AG00005Q	100-18-5	23975g	98%	5 days	5G	10
CC(c1ccc(cc1)C(C)C)C	Benzene, 1,4-bis(1-methylethyl)-	AG00005Q	100-18-5	23975g	98%	5 days	10G	12
CC(c1ccc(cc1)C(C)C)C	Benzene, 1,4-bis(1-methylethyl)-	AG00005Q	100-18-5	23975g	98%	5 days	25G	15
CNc1ccc(cc1)[N+](=O)[O-]	Benzenamine, N-methyl-4-nitro-	AG00005T	100-15-2	3450g	97%	5 days	5G	6
CNc1ccc(cc1)[N+](=O)[O-]	Benzenamine, N-methyl-4-nitro-	AG00005T	100-15-2	3450g	97%	5 days	10G	7
CNc1ccc(cc1)[N+](=O)[O-]	Benzenamine, N-methyl-4-nitro-	AG00005T	100-15-2	3450g	97%	5 days	25G	9

I feel I need to use the CrossTable function but as I'm new to Qlik Sense I'm not 100% sure how to use it correctly to get the above result, or even if this is the right option to do this.

Any help would be much appreciated.

Thanks
Paul

[justISO · Accepted solution]

Hi, unfortunately, as I know, crosstable is working only with one attribute field, and in your case it is two, Quantity and Prices. So you need to load and do crosstable your table 2 times, one for Quantity and one for Prices and 'merge' them together. Not so elegant solution, but you can try something like this:

quantity_temp:
CrossTable(quantity, value, 8)
LOAD
    RowNo() as ROW,
    SMILES, Chemical_Name, Catalog_Number, CAS_Number, Inventory, Purity, Leadtime,
    Quantity1,
    Quantity2,
    Quantity3,
    Quantity4,
    Quantity5,
    Quantity6,
    Quantity7,
    Quantity8,
    Quantity9,
    Quantity10
FROM [lib://AttachedFiles/Original file.xlsx]
(ooxml, embedded labels, table is Sheet1);

prices_temp:
CrossTable(prices, value, 8)
LOAD
    RowNo() as ROW,
    SMILES, Chemical_Name, Catalog_Number, CAS_Number, Inventory, Purity, Leadtime,
    Prices1,
    Prices2,
    Prices3,
    Prices4,
    Prices5,
    Prices6,
    Prices7,
    Prices8,
    Prices9,
    Prices10
FROM [lib://AttachedFiles/Original file.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
main:
LOAD
    SMILES, Chemical_Name, Catalog_Number, CAS_Number, Inventory, Purity, Leadtime,
    ROW &'-'& KeepChar(quantity,'0123456789') as KEY,
    value as Quantity
RESIDENT quantity_temp;

LEFT JOIN
LOAD
    ROW &'-'& KeepChar(prices,'0123456789') as KEY,
    value as Prices
RESIDENT prices_temp;

DROP TABLES quantity_temp, prices_temp;
// DROP FIELD KEY FROM main; //uncomment row to drop KEY field

Notice, than I make additional field ROW and keeping digits from attribute names to make a KEY which I use to connect 2 tables into one.

[PaulK]

Thank you @justISO 

Your solution worked perfectly.

Kind Regards
Paul