Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sudhakarsamak06
Contributor
Contributor

Modify a field value based on a condition involving another field

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?

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

3 Replies
Vegar
MVP
MVP

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;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
sudhakarsamak06
Contributor
Contributor
Author

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
MVP
MVP

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
Plees ekskuse my Swenglish and or Norweglish spelling misstakes