topic Re: Excluding String b from String A in qlik sense in App Development

Excluding String b from String A in qlik sense

vikasmahajan — Mon, 07 Nov 2022 05:14:41 GMT

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

Re: Excluding String b from String A in qlik sense

rubenmarin — Mon, 07 Nov 2022 07:39:32 GMT

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

Re: Excluding String b from String A in qlik sense

vikasmahajan — Mon, 07 Nov 2022 08:22:32 GMT

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

Re: Excluding String b from String A in qlik sense

rubenmarin — Mon, 07 Nov 2022 08:52:27 GMT

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))"

Re: Excluding String b from String A in qlik sense

vikasmahajan — Mon, 07 Nov 2022 09:24:56 GMT

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

Re: Excluding String b from String A in qlik sense

N30fyte — Mon, 07 Nov 2022 10:59:50 GMT

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.

Re: Excluding String b from String A in qlik sense

rubenmarin — Mon, 07 Nov 2022 11:11:32 GMT

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/1981049

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

Re: Excluding String b from String A in qlik sense

MarcoWedel — Mon, 07 Nov 2022 11:41:13 GMT

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

Re: Excluding String b from String A in qlik sense

vikasmahajan — Tue, 08 Nov 2022 05:57:02 GMT

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

Re: Excluding String b from String A in qlik sense

rubenmarin — Tue, 08 Nov 2022 07:18:21 GMT

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?

Re: Excluding String b from String A in qlik sense

vikasmahajan — Tue, 08 Nov 2022 08:10:43 GMT

I am using Vizlib Custom Reporting extension with 72 dimensions there I could not be able to use data islands for ad-hoc , so for at least 10 levels need to calculate market share dynamically with excluding last selected dimension by user.

Thanks

Vikas