Get correct field value in Qlik sense

[GraceGao]

Hi Experts

as per below screenshot, there is sales table ,price system table and price manual maintain table.

in the load script, i left join 3 tables and calculate qty=sales/price,however the result is not my expected.

user want to retrieve material's price in the system first, and system has not this material's price then to lookup the maintain table's price. however, if one day, the system maintained material price in system, and user could not aware this, the material price exist in both price system table and manual maintain table, then the sales amount is double,the calculation is wrong. 

could you please advise, how can i do this logic in the script editor?

Thanks.

[gmenoutis · Accepted solution]

If I understand correctly, if the material exists in both [Price Table] and [Price Temp Table], you want to only consider the [Price Table] values. You can do this with careful load order and a WHERE clause.

1) Load the [Price Table] *before* loading the sales table

[Price Table]: load ............ from ....(price table source)

2) Do a concatenate load from price temp table only for the materials who are not already present in price table:

concatenate load([Price Table]) .... from ....(price temp table source) where not exists([Material])

Now you only have one table, [Price Table], which has one price for each material.

3) Load sales table as usual

4) Join sales table and price table (there is no third table to join)

[GraceGao · Accepted solution]

Hi gmenoutis

Thanks so much. it is working .