Excluding String b from String A in qlik sense

[vikasmahajan]

Hi Team,

VarA  ='Brand_Generic,Super_Channel,Channel,MonthYear';

VarB = 'Channel';

I wanted to exclude/ Remove string VarB from VarA how to achieve the same?

Thanks

Vikas

[rubenmarin]

Hi, may be using Replace(): VarC = Replace('$(VarA)','$(VarB)','');

[vikasmahajan]

Hi Ruben,

Thanks for your answer  I applied the same I am losing Super_Channel  as well from the string any way to avoid this ?

Thanks

Vikas

[rubenmarin]

Hi Vikas, ok, just the member 'Channel and not substrings...

I'm thinking in splitting every row in different rows using subfield and then merge again using "Where Value<>'$(varA)'", like:

auxTable:
LOAD Concat(Value,',') as Value Where Value<>'$(VarB)';
LOAD Subfield('$(VarA)',',') as Value AutoGenerate 1;

LET VarC = Peek('Value',0,'auxTable');
DROP Table auxTable;

If instead of variables there is a table you can use RowNo() as RN and group by RN in the precedent load to merge again the groups.

And if there are different values for VarB you can use "Where not Match(Value,$(ListOfValues))"

[vikasmahajan]

Unfortunately Load script will not work for me since My Custom reporting dimensions are dynamic 72 dimensions i have & I need set a limit of 10 dimensions only while calculating market share.

Thanks

Vikas

[N30fyte]

If you want to use Replace(), you also need to remove the comma character that precedes 'Channel'.

varA:  = 'Brand_Generic,Super_Channel,Channel,MonthYear'

varB:  = 'Channel'

varC:  = ','&varB 

varD : Replace(varA,varC,'')

This declares the two basic variables, then concatenates a comma and varB, and then replaces the concatenation with an empty string.

In the expression editor you wouldneed to use a dollar-sign expansion with varD:

=$(varD)

Variables in alphabetical order below, displayed in Text & Image chart items.

Hope this helps.

[rubenmarin]

Hi, I've very questions on how this works, first of all I'm not sure if chart script can help you to have the power of script when filtering dimensions: https://community.qlik.com/t5/Design/Chart-Level-Scripting-Use-Cases-Samples-and-Examples/ba-p/19810...

And the questions:

-it's a custom reporting done with a selector of dimensions and a selector of measures?

- why remove channel? are there some priorities for dimensions defined somewhere?

- Can it be crated as MinString(DimensionField1) & ',' & MinString(DimensionField2)...? or similar.

A very straightforward expression could be:

VarC = Left('$(VarA)', Index('$(VarA)',',$(VarB)')) & Mid('$(VarA)', Index('$(VarA)',',$(VarB)') + Len(',$(VarB)') + 1);

But I don't know if this might work with your real scenario

[MarcoWedel]

=Mid(Replace(','&VarA&',' , ','&VarB&',' , ','),2,Len(Replace(','&VarA&',' , ','&VarB&',' , ','))-2)

[vikasmahajan]

Hi Macro/Ruben,

Thanks for your reply I ended up with following logic :

=if(Len(GetObjectField(10)) >0 and Len(GetObjectField(9)) > 0 and Len(GetObjectField(8)) > 0 and Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(6)&','&GetObjectField(7)&','&GetObjectField(8)&','&GetObjectField(10),
if(Len(GetObjectField(9)) > 0 and Len(GetObjectField(8)) > 0 and Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(6)&','&GetObjectField(7)&','&GetObjectField(9),
if(Len(GetObjectField(8)) > 0 and Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(6)&','&GetObjectField(8),
if(Len(GetObjectField(7)) > 0 and Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(5)&','&GetObjectField(7),
if(Len(GetObjectField(6)) > 0 and Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(4)&','&GetObjectField(6),
if(Len(GetObjectField(5)) > 0 and Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(3)&','&GetObjectField(5),
if(Len(GetObjectField(4)) > 0 and Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(2)&','&GetObjectField(4),
if(Len(GetObjectField(3)) > 0 and Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(1)&','&GetObjectField(3),
if(Len(GetObjectField(2)) > 0 and Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(0)&','&GetObjectField(2),
if(Len(GetObjectField(1)) > 0 and Len(GetObjectField(0)) > 0, GetObjectField(1),
If(Len(GetObjectField(0)) > 0 ,GetObjectField(0)
,0)))))))))))

[rubenmarin]

Yep, short and simple 😁

Just because of curiosity, probably because of how the chart is created but... if GetObjectField(10)) >0 doesn't means that all the other dimensions until 10 will be have something too and is not really needed to check?

And the thing about removing the previous to the last, it's because it's assumed that Channel will be always in that position?