2

I need to create a 4th column in either table as a sum of columns 1,2 and 3. How can I do it?.

LOAD column1, 
     KeyColumn 
FROM
[C:\Qlik\file1.xlsx]
 (ooxml, embedded labels, table is sheet1);

LOAD column2, 
     column3, 
     KeyColumn 
FROM
[C:\Qlik\file1.xlsx]
(ooxml, embedded labels, table is sheet2);
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
jotape
  • 319
  • 2
  • 6
  • 14

1 Answers1

1

I changed your LOAD script into a INLINE version, so it is not depending on external files and other users can copy and paste it easy.

My LOAD script:

Data:
LOAD * INLINE [
column1, KeyColumn
1, Product_A
2, Product_B
3, Product_C
];

OUTER JOIN
LOAD * INLINE [
column2, column3, KeyColumn
10, 100, Product_A
20, 200, Product_B
30, 300, Product_C
];

I use the outer join to be sure that all values for KeyColumn are in the resulting table.

In the dashboard I load all available columns and get:

three columns

Next step is to iterate over the existing values and add the three columns and store them in column4.

LOAD  
Num#(column1 ,'##.###') + Num#(column2 ,'##.###') + Num#(column3 ,'##.###') as column4
Resident Data;

Attention. This

column1 + column2 + column3  as column4

will not work!

After adding the new column to the dashboard you get:

four columns

I hope thats what you were asking for.

Update after comments.

In your case

Data:
LOAD column1, 
     KeyColumn 
FROM
[C:\Qlik\file1.xlsx]
 (ooxml, embedded labels, table is sheet1);

OUTER JOIN 
LOAD column2, 
     column3, 
     KeyColumn 
FROM
[C:\Qlik\file1.xlsx]
(ooxml, embedded labels, table is sheet2);

LOAD  
Num#(column1 ,'##.###') + Num#(column2 ,'##.###') + Num#(column3 ,'##.###') as column4
Resident Data;

should work.

smartmeta
  • 1,149
  • 1
  • 17
  • 38
  • Ok, but if I need to use an external file? I've simplified the example; but both tables (sheets) have lots of rows. – jotape Nov 27 '14 at 16:20
  • 1
    The way you load the data does not matter. Just add the third LOAD statement and you should be fine. – smartmeta Nov 27 '14 at 18:14
  • I've tried but didn´t work. Perhaps I wasn´t clear. From your example, a need a bar char with the number 666. This number is the sum of all the columns. – jotape Nov 29 '14 at 17:34
  • If your requirement changed, what about asking a new question? – smartmeta Nov 30 '14 at 07:54