Conditional import from one table based on data in another

[#1]

Hi all,

I have a problem that should, I believe, be relatively simple but I'm such a beginner that I'm sure I'm missing something.

I am importing data from two tables in my LOAD statement;

ITEM, which has product number, category, cost, etc.

TRANSACTION, which details movement of products. Two of its fields are TRANSFER-TO and TRANSFER-FROM. If the TRANSFER-TO field is SCRAP, then this is money lost to the scrapping process, and the total of all entries with this condition would be total lost to scrap. However, sometimes items can be transferred back from SCRAP into other departments, in which case the COST, though listed as positive in the ITEM table, should be subtracted from the calculated total scrapped amount.

If everything was going to be imported from one table, I'd just put an IF statement in the load making COST negative when TRANSFER-FROM was SCRAP, and then make the total the sum of the transactions. However, the COST is only from the ITEM table. How do I modify the data coming from that table based on a condition in a linked table?

I HOPE I explained that well enough... thanks!

Jonathan

[#2]

Hi Jonathan,

Usually when i need to compare data from two tables without wanting to join them do i normally use mapping load. which creates a temporary table that can be referenced and give you additional data or instance item cost.

it could look something like this:

Map_Table:

mapping load

ItemNo,Cost

resident Tab1;

In the load statement where you need additional data would i call for that temporary table and will be able to insert additional data based on a field value that is matched.

applymap('Map_Table,ItemNo) as ItemCost,   //this will give you the cost for every itemNo.

Might be worth a try?

Peek function is also an inter record function that is used for the similar purpose: http://help.qlik.com/sense/2.1/en-US/online/#../Subsystems/Hub/Content/Scripting/InterRecordFunction...

BR

Fredrik

[brunobertels]

Hi

May be with a sample of your 2 tables , or directly a .qvf , il would be easier to bring advise.

Nethertheless for my opinion i would load the 2 tables a tempdate, then join the 2 tables in only one so that i can add a calculated dimension with an IF statement for new calculation.

have a look to this link maybe :

https://community.qlik.com/message/901613#901613

Or search in the forum with those keyword : "join table for calculation"

Hope it helps

Regards

Bruno