topic Adding calculated columns with data load editor in App Development

Adding calculated columns with data load editor

Tue, 11 Aug 2015 09:56:52 GMT

Hello,

I use Qlik Sense and try to add a calculated column to my data.

I use data load editor.

The script in auto-generated section is:

LOAD

Product,

"Date",

Amount

FROM [lib://QilkViewData/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

I append to the Main section:

LOAD

Month("Date") as vMonth

FROM [lib://QilkViewData/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

then I reload my data.

It gives me vMonth column but when I plot bar chart sum(Amount) vs vMonth, it gives me constant values of sum(Amount) for all the data (see attached project). How do I correct it?

Also, when I try to add a column with complex formula (e.g. sum({<Product = {"A"}>}Amount as vACost), it gives me errors. How do I add a such a column to my data?

Re: Adding calculated columns with data load editor

awhitfield — Tue, 11 Aug 2015 10:01:48 GMT

Hi Egor,

can you also upload your xlsx too please?

Andy

Re: Adding calculated columns with data load editor

jonathandienst — Tue, 11 Aug 2015 10:12:23 GMT

vMonth is not associated in any way with the table containing Amount, so if you use vMonth as a dimension you will always get total value.

Re: Adding calculated columns with data load editor

jonathandienst — Tue, 11 Aug 2015 10:14:02 GMT

sum({<Product = {"A"}>}Amount as vACost) is not a valid expression.

In script, you cannot use set analysis. In an expression, you should write

sum({<Product = {"A"}>}Amount)

and name it vACost

Re: Adding calculated columns with data load editor

Tue, 11 Aug 2015 10:14:28 GMT

Andrew, ok see attachement.

Re: Adding calculated columns with data load editor

Tue, 11 Aug 2015 10:17:44 GMT

Jonathan,

how do I associate it with my data?

Re: Adding calculated columns with data load editor

awhitfield — Tue, 11 Aug 2015 10:30:49 GMT

HI Egor,

checkout the attached

HTH - Andy

Re: Adding calculated columns with data load editor

Tue, 11 Aug 2015 10:36:59 GMT

Jonathan,

thank you for your answer,

sorry I mistyped, I meant sum({<Product = {"A"}>}Amount) as vACost

ok how do I use an expression sum({<Product = {"A"}>}Amount) for further calculations?

e.g.

ACost = sum({<Product = {"A"}>}Amount)

currentACost = sum({<Date = {"=Today()"}ACost)

Re: Adding calculated columns with data load editor

Tue, 11 Aug 2015 10:41:28 GMT

Andrew,

thank you! That's the correct answer.

But how did you delete the auto-generated section?

Re: Adding calculated columns with data load editor

Tue, 03 Nov 2015 02:32:41 GMT

I am using older version of Qlik and not able to open the file. I'd like to see the example here, can anyone help? TIA.