Modify a field value based on a condition involving another field

[sudhakarsamak06]

Hello,

I was stuck with a problem and wanted help with it. 

I have loaded a excel files into Qlikview and joined 3 tables to form one. Now i want to modify a field in one table based on a condition that involves a field from another table. 

My script looks like below

Load custID, Country from ....

INNER JOIN()

Load custID, Status from .......

INNER JOIN()

Load custID, Amount from .....

Here i want to change all the values of amount to zero when the status is equal to 'Inactive'. Can someone please help me out with this?

[Vegar · Accepted solution]

You could do a resident load from your joined table.

TMP:
Load custID, Country from ....

INNER JOIN(TMP)
Load custID, Status from .......

INNER JOIN(TMP)
Load custID, Amount from ..

FinalData:
NoConcatenate LOAD 
  custID,
  Country ,
  Status ,
  if(Status='inactive', 0,Amount) as Amount 
Resident 
  TMP;

DROP TABLE TMP;

[sudhakarsamak06]

Worked perfectly fine. Looks like the NoConcantenate option is very important. I had tried the same code without NoConcatenate and it didn't create the new table. 

Thank you very much for your help!

[Vegar]

Yes, NoConcatenate is important in cases where you want to 'replicate' a
table or create a new table with identical field names.

If you're unsure about why I'm using NoConcatenate or the concept of
AutoConcatenate please read this help page:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/LoadData/concat...

Cheers
Vegar