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

[Sai_Mohan]

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. 

Family	Item	Products
Fruits	Apple	Apple
fruits	apple	orange
Fruits	apple	banana
vegetable	carrot	carrot
vegetable	onion	onion
null	null	apple, orange
null	null	apple, orange,banana
null	null	carrot,onion
Here, instead of getting null values for last few columns, we need to get  their corresponding values.
Expected Output:
Family	Item	Products
Fruits	Apple	Apple
fruits	apple	orange
Fruits	apple	banana
vegetable	carrot	carrot
vegetable	onion	onion
fruits,fruits	apple,apple	apple, orange
fruits,fruits,fruits	apple, apple, apple	apple, orange,banana
vegetable, vegetable	carrot,onion	carrot,onion

Thanks In advance!

[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]

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]

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]

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;