Selection against Associative Technology

[#1]

Hi All,

I am stuck with one requirement from customer and need help. Detail below.

I have data like this -

Data
Client	Prod lvl1	Prod Lvl2	Prod Lvl3	Prod Lvl4	wallet
1	A	B	C	D	1.8
1	A	B	C		2.3
1	A	B			3
1	A				2
Client	Prod lvl1	Prod Lvl2	Prod Lvl3	Prod Lvl4	wallet
1	P	Q	R	S	10
1	P	Q	R		4
1	P	Q			6
1	P				5

Now I want to display data into table based on selection - yellow color from list box. Like this

List Box	List Box	List Box	List Box
Prod lvl1	Prod Lvl2	Prod Lvl3	Prod Lvl4
A	B	C	D
P	Q	R	S

Final Straight Table Need -
Client	Prod lvl1	Prod Lvl2	Prod Lvl3	Prod Lvl4	wallet
1	A	B	C	D	1.8
1	P	Q	R		4

Note that Prod Lvl4 = "D" belongs to Prod hierarchy "A" & Prod Lvl3 = "R" belongs to different prod hierarchy "P".

Any help on expression side using "if else" OR "set analysis" or anything would be great help. I am trying to use Data Island concept but might my script is not giving right answer. thank you.

[Gysbert_Wassenaar]

You'll need to use alternate states because without those when you select D in the listbox for Prod Lvl4 then the value R will be excluded in the listbox for Prod Lvl3.

Create two alternates states on the General tab of the Document Properties window. Then assign the alternate states to the listboxes on the General tab of the listbox properties windows. Then you can use the alternates states in a set analysis expressions in a straight table with Client, Prod lvl1, Prod Lvl2, Prod Lvl3, and Prod Lvl4 as dimensions and as expression sum({AltState1+AltState2}wallet)

[#3]

Thanks Gysbert for your response.

I tried you suggestion but still I am not getting 2 rows of data. I need 1 row for "D" and another row for "R". Infact now I am not able to filter my straight table based on selection on list box Prod Lvl3 & Prod Lvl4 which has different alternate state.

I just want to show values in individual row using only(wallet). Please suggest if I miss anything.

thanks.

[#4]

Hi Gysbert,

I have attached QV file for your reference. Please see and let me know what is missing here so that I am not able to select products from two different hierarchy using alternate states.

thank you.

[Gysbert_Wassenaar]

See attached qvw.

[#6]

Thanks for your reply again.

Two things still pending to achieve here-

1. If I select any prod say "B" my grid should give me only one row which has value 3 and if I select "R", my grid should show another row having value "4". Show finally I have two rows in grid for two production selection from different hierarchy. I need data shown in yellow color in grid.

2. If I don't selection any product filters. I want all data to be present in the grid/table.

Hope you got my requirement and thank you again for putting your time and help.

[Gysbert_Wassenaar · Accepted solution]

1. Those are completely arbitrary selections to me. I don't think there's any logical way to implement that.

2. Change the variable to

=

if(

  len(GetCurrentSelections('','','',6,'ProdLvl1')) or

  len(GetCurrentSelections('','','',6,'ProdLvl2')) or

  len(GetCurrentSelections('','','',6,'ProdLvl3')) or

  len(GetCurrentSelections('','','',6,'ProdLvl4'))

,

if(GetSelectedCount([Prod lvl1],0,'ProdLvl1'),'ProdLvl1','Dummy') & '+' &

if(GetSelectedCount([Prod Lvl2],0,'ProdLvl2'),'ProdLvl2','Dummy') & '+' &

if(GetSelectedCount([Prod Lvl3],0,'ProdLvl3'),'ProdLvl3','Dummy') & '+' &

if(GetSelectedCount([Prod Lvl4],0,'ProdLvl4'),'ProdLvl4','Dummy')

,'$')

[#8]

Thanks Gysbert,

I know whatever I did was not good practice but achieved my output using following code -

if(GetSelectedCount(Level_1,0,'Prod1')=0 and GetSelectedCount(LEVEL_2,0,'Prod2')=0 and GetSelectedCount(LEVEL_3,0,'Prod3')=0,only(WLLT_SZ_MN),
if(
purgechar(if(GetSelectedCount(Level_1,0,'Prod1')<>0 and IsNull(LEVEL_2) and IsNull(LEVEL_3) and IsNull(LEVEL_4), only({<Level_1={$(=vL1)} >}WLLT_SZ_MN))
& '+' &
if(GetSelectedCount(LEVEL_2,0,'Prod2')<>0 and IsNull(LEVEL_3) and IsNull(LEVEL_4), only({<LEVEL_2={$(=vL2)} >}WLLT_SZ_MN))
& '+' &
if(GetSelectedCount(LEVEL_3,0,'Prod3')<>0 and IsNull(LEVEL_4), only({<LEVEL_3={$(=vL3)} >}WLLT_SZ_MN)),
'++')
>0,
(purgechar(if(GetSelectedCount(Level_1,0,'Prod1')<>0 and IsNull(LEVEL_2) and IsNull(LEVEL_3) and IsNull(LEVEL_4), only({<Level_1={$(=vL1)} >}WLLT_SZ_MN))
& '+' &
if(GetSelectedCount(LEVEL_2,0,'Prod2')<>0 and IsNull(LEVEL_3) and IsNull(LEVEL_4), only({<LEVEL_2={$(=vL2)} >}WLLT_SZ_MN))
& '+' &
if(GetSelectedCount(LEVEL_3,0,'Prod3')<>0 and IsNull(LEVEL_4), only({<LEVEL_3={$(=vL3)} >}WLLT_SZ_MN)),
'++')
))
)

But anyways thanks alot for putting your light on what ways this can be doable (Alternate States).

[#9]

Hi Gysbert,

One help needed, can you tell me how to get associated data as well.

Ex-

If I select lowest level "S" and then if I select one level above "R". I am getting two record - One for "S" value & other for "R" value. I want if same hierarchy data, then show only lowest value . Here I should have got only Record having wallet 10.

same example again-

If I select lowest level "R" and then if I select one level above "Q". I am getting two record - One for "R" value & other for "Q" value. I want if same hierarchy data selected, then show only lowest value . Here I should have got only Record having wallet 4.

Thank you again for your help