topic Re: Modify a field value based on a condition involving another field in QlikView

Modify a field value based on a condition involving another field

sudhakarsamak06 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Modify a field value based on a condition involving another field

Vegar — Thu, 20 Jun 2019 22:01:36 GMT

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;

Re: Modify a field value based on a condition involving another field

sudhakarsamak06 — Fri, 21 Jun 2019 15:04:48 GMT

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!

Re: Modify a field value based on a condition involving another field

Vegar — Fri, 21 Jun 2019 22:27:05 GMT

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/concatenate-tables.htm

Cheers
Vegar