Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai_Mohan
Contributor III
Contributor III

How to get values for other column based on one column?

Hello Team,

I am new to QlikSense. Please help me with this.

How can I get values for the multiple values. In excel attached below, Product column has some comma separated values. For other two columns, there are only single value. 

My question is I am getting null values for First two columns, If I have comma separated values in Products column. Please help me to get values for first two values.

I am loading data from DB and can I able to use exists() or Match Function? 

As the fruit and Item column has single value in db, so it recognize only single value for Product column also(Which comes other table). I need values for Fruit and Item column instead of null. 

I have attached sample output data. 

FamilyItem Products
FruitsAppleApple
fruitsapple orange
Fruitsapplebanana
vegetablecarrotcarrot
vegetableoniononion
nullnullapple, orange
null null apple, orange,banana
nullnullcarrot,onion
   
   
Here, instead of getting null values for last few columns, we need to get  their corresponding values.
   
Expected Output:  
   
FamilyItem Products
FruitsAppleApple
fruitsapple orange
Fruitsapplebanana
vegetablecarrotcarrot
vegetableoniononion
fruits,fruitsapple,appleapple, orange
fruits,fruits,fruitsapple, apple, appleapple, orange,banana
vegetable, vegetablecarrot,onioncarrot,onion

 

 

Thanks In advance!

Labels (3)
7 Replies
Kushal_Chawda

@Sai_Mohan  try below

Family_Map:
mapping LOAD Distinct Products,
              Family
FROM TableA
where not index(Products,',');

Final:
NoConcatenate
load *,
     if(len(trim(Family))=0, MapSubString('Family_Map',Products),Family) as New_Family
FROM TableA;
Sai_Mohan
Contributor III
Contributor III
Author

Hi @Kushal_Chawda,

I have columns same like Family which needs to be included in the code. the columns are item and status.

How that will be included in it.

Should I need to create new mapsubstring function for each column?

Kushal_Chawda

@Sai_Mohan  please share a sample with expected output

Sai_Mohan
Contributor III
Contributor III
Author

Hi @Kushal_Chawda ,

Thanks for replying.

I have given every change and wrong output, which needs to be changed ,in that sample file.   please look into it and help me asap.

 

Thanks in advance!

Kushal_Chawda

@Sai_Mohan  see the attached

Sai_Mohan
Contributor III
Contributor III
Author

I couldn't able to open this. I am working on the qliksense. so please help me

Kushal_Chawda

@Sai_Mohan  problem is that your products field contains different upper and lower cases value like eg. apple,Apple. due to which you are getting wrong results. try below script

Map_BL:
mapping LOAD Distinct  trim(lower(Products)) as Products,
      BL
FROM TableA
where len(trim(BL))>0;

Map_Plat:
mapping LOAD Distinct  trim(lower(Products)) as Products,
      Plat
FROM TableA
where len(trim(Plat))>0;

Map_Family:
mapping LOAD Distinct  trim(lower(Products)) as Products,
      Family
FROM TableA
where len(trim(Family))>0;

Data:
 LOAD Products,
      if(len(trim(Family))=0, MapSubString('Map_Family',trim(lower(Products))),Family) as Family_New,
      if(len(trim(Plat))=0, MapSubString('Map_Plat',trim(lower(Products))),Plat) as Plat_New,
      if(len(trim(BL))=0, MapSubString('Map_BL',trim(lower(Products))),BL) as BL_New  
FROM TableA;