Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

posywang
Contributor

How to change values when two tables joined?

Hi,

I have two tables and the 2nd table left join the first one. My question is how to change value in Quality based on value in Product?

For example, if Product name starts with "C", change value of Quality to "Bad". Please let me know. Thanks.

   

Table A
Prod IDProductType
1AppleFruit
2BananaFruit
3BikeVehicle
4CarVehicle
5CatAnimal

   

Table B
TypeSourceQuality
FruitUSAAmazing
VehicleChina

Great

AnimalMexicoAwesome
1 Solution

Accepted Solutions
MVP
MVP

Re: How to change values when two tables joined?

Maybe use a MAPPING approach:

Don't join - use Applymap instead

MAP:

MAPPING

LOAD Type, Source & '|' & Quality

FROM TableB;

LOAD

[PROD ID], Product, Type,

     If(Left(Product), 'C', 'Bad', SubField(ApplyMap('MAP', Type),'|',2) ) as Quality,

    SubField(ApplyMap('MAP', Type),'|',1) ) as Source

FROM TABLEA;

2 Replies
MVP
MVP

Re: How to change values when two tables joined?

Maybe use a MAPPING approach:

Don't join - use Applymap instead

MAP:

MAPPING

LOAD Type, Source & '|' & Quality

FROM TableB;

LOAD

[PROD ID], Product, Type,

     If(Left(Product), 'C', 'Bad', SubField(ApplyMap('MAP', Type),'|',2) ) as Quality,

    SubField(ApplyMap('MAP', Type),'|',1) ) as Source

FROM TABLEA;

posywang
Contributor

Re: How to change values when two tables joined?

I added null() to formula since I do have some blank data but otherwise the solution is perfect! Thanks!

Community Browser