Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madnanansari
Creator
Creator

Calculated column in line table based on master table column

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.

2 Replies
sunny_talwar

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];

sunny_talwar

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];