Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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];