topic Calculated column in line table based on master table column in QlikView

Calculated column in line table based on master table column

madnanansari — Sat, 06 Feb 2016 13:44:12 GMT

I have sales master and sales detail table. Both are available in qvd format.

Sales Master has invoice no, customer no, discount % and other columns.

Sales detail has invoice no, line no, item no, quantity and sales value and other columns.

I would like to add a discount value column in sales detail table where the calculation formula is: discount value = sales value * (discount % /100).

how this is achieved in the script when I load the data.

Within my data model I have created only one sales table where I first load the sales master and then left join it with the sales detail table based on invoice no field.

Re: Calculated column in line table based on master table column

sunny_talwar — Sat, 06 Feb 2016 15:15:16 GMT

May be something like this:

MappingTable:

Mapping

LOAD [invoice no],

[discount %]

FROM [Sales Master];

[Sales Detail]:

LOAD [invoice no],

[line no],

[item no],

quantity,

[sales value],

[sales value] * ApplyMap('MappingTable', [invoice no]) as [discount value]

FROM [Sales Detail];

Re: Calculated column in line table based on master table column

sunny_talwar — Sat, 06 Feb 2016 15:16:32 GMT

And if you are already left joining them, you can disregard the above suggestion and simply perform your calculation in a resident load of joined tables.

Sales Master has invoice no, customer no, discount % and other columns.

Sales detail has invoice no, line no, item no, quantity and sales value and other columns.

[Sales Master]:

LOAD [invoice no],

[discount %],

[other columns]

FROM [Sales Master];

Left Join ([Sales Master])

LOAD [invoice no],

[line no],

[item no],

quantity,

[sales value]

FROM [Sales Detail];

FinalTable:

LOAD *,

[sales value] * [discount %]/ as [discount value]

Resident [Sales Master];

DROP Table [Sales Master];