topic Re: Possible Multiple If condition in QlikView

Possible Multiple If condition

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need help. I have transaction data that are in group Tender Board A, Tenders Board B, Tenders Board C. In which have approving authority (name 1 ,name 2,name 3)

transaction Number	Tender	Name 1	Name 2	Name 3
0000000001	Tender Board A	Roy	Charanjit	Penn
0000000002	Tenders Board B	Penn	Mun	Ling
0000000003	Tenders Board B	Penn	Mun	Charanjit
0000000004	Tenders Board B	Penn	Mun	Ling
0000000005	Tender Board C	Paul	Charanjit	Mun
0000000006	Tender Board C	Paul	Steph	Annie
0000000007	Tender Board C	Ling	Steph	Annie
0000000008	Tender Board A	Roy	Mun	Charanjit

In my qlikview, I have an image(text box) which will appear if conditions is made.

The CONDITIONS are:

User Selected transaction number,

If is group "Tenders Board C", Annie and Steph IS NOT in the 3 names, the image will appear,

If is group "Tenders Board B", Ling IS NOT in the 3 names, the image will appear,

If is group "Tender Board A", Ling IS NOT in the 3 names, the image will appear,

RESTRICTIONS:

The expression should be in the text box and not the script as originally the name are generated after running the script.

Attach are the data, same as the table above.

I am unable to view .qvw due to personal edition. A screenshot is kindly appreciated

Re: Possible Multiple If condition

sunny_talwar — Wed, 30 Nov 2016 08:52:29 GMT

What do you mean if is Group? Do you mean that the user will make a selection in particular group before the image will be viewed?

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 08:57:51 GMT

Hi, thanks for the quick reply.

The user will select the transaction number. In fact, my original have a search box will user will enter the transaction number. Once the transaction number is selected (searched), the image will be viewed based on the conditions.

Re: Possible Multiple If condition

hemanthaanichet — Wed, 30 Nov 2016 08:58:35 GMT

Hi Jia,

Could you elaborate the conditions once again

i have a doubts in that let me what is that not in the 3 names in your conditions

if u can provide what is your expected output that will be more helpful

Regards

Hemanth

Re: Possible Multiple If condition

MK_QSL — Wed, 30 Nov 2016 09:00:28 GMT

Like this?

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 09:03:44 GMT

Hi, thks for the quick reply.

I am unable to view it due to personal edition. Are you able to screenshot the expression instead?

Thank you!

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 09:05:55 GMT

Hi, thks for the quick reply as well,

for example,

when User selected transaction: 0000000005, the image will appear as Steph and Annie is not in the 3 names(Paul,Charanjit,Mun).

When User selected transaction:0000000006, the image will not appear as Steph and Annie is in the 3 names.

Re: Possible Multiple If condition

MK_QSL — Wed, 30 Nov 2016 09:06:44 GMT

I have used below script

CrossTable(NO, Name,2)

LOAD [transaction Number],

Tender,

[Name 1],

[Name 2],

[Name 3]

FROM

[Example tessting.xlsx]

(ooxml, embedded labels, table is Sheet1);

Now Create Three Text Boxes

Type below condition to layout tab of each text box... conditional show ...

For A

=IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tender Board A'}>}DISTINCT Name, '|')&'|'),'|LING|'),0,1)

For B

=IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tenders Board B'}>}DISTINCT Name, '|')&'|'),'|LING|'),1,0)

For C

=IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tender Board C'}>}DISTINCT Name, '|')&'|'),'|ANNIE|') and SubStringCount('|'&Upper(Concat({<Tender = {'Tender Board C'}>}DISTINCT Name, '|')&'|'),'|STEPH|'),0,1)

Make sure to check the typo..

You have used

Tender Board A

Tenders Board B

Tender Board C

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 09:10:22 GMT

Hey, can you please elaborate your condition little more.

Thanks,

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 09:20:36 GMT

Hi, I tried. it's close.

When nothing is selected, box A and B did not disappear.

When Transaction 1 is selected, box C should no appear as transaction is Tender Board A and not Tender Board C.

Thks for the typo head up as well.

Re: Possible Multiple If condition

MK_QSL — Wed, 30 Nov 2016 09:22:11 GMT

If nothing is selected... what you want?

All three box should appear?

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 09:25:21 GMT

The CONDITIONS are:

User Selected transaction number,

If is group "Tenders Board C", Annie and Steph IS NOT in the 3 names, the image will appear,

If is group "Tenders Board B", Ling IS NOT in the 3 names, the image will appear,

If is group "Tender Board A", Ling IS NOT in the 3 names, the image will appear.

for example,

User selected transaction: 0000000001, the image will not appear as Ling as is not in the 3 names (tender Board A).

User selected transaction: 0000000002, the image will appear as Ling is in the 3 names (tender Board B).

User selected transaction: 0000000003, the image will not appear as Ling as is not in the 3 names (tenders Board B).

User selected transaction: 0000000004, the image will appear as Ling is in the 3 names (tenders Board B).

User selected transaction: 0000000005, the image will appear as Steph and Annie is not in the 3 names (tender Board c).

When User selected transaction:0000000006, the image will not appear as Steph and Annie is in the 3 names. (tender Board C).

Re: Possible Multiple If condition

Anonymous — Wed, 30 Nov 2016 09:33:03 GMT

When nothing is selected, the 3 box should disappear. But I fix that with if(GetSelectedCount([transaction Number])=1,

IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tenders Board B'}>}DISTINCT Name, '|')&'|'),'|LING|'),1,0),0)

Box C does not disappear when the transaction is not related to Tender Board C.

When transaction with Tender board C(5,6&7) is selected, Box A & B disappear. But when transaction with Tender Board A and B is selected, Box C does not disappear.

Re: Possible Multiple If condition

MK_QSL — Wed, 30 Nov 2016 09:57:18 GMT

Use below conditions for

IF(GetSelectedCount(Tender) = 0 and GetSelectedCount([transaction Number])=0, 0,

IF(SubStringCount(Concat(DISTINCT Tender,','),'Tender Board A')=0,0,

IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tender Board A'}>}DISTINCT Name, '|')&'|'),'|LING|')=0,0,1)))

IF(GetSelectedCount(Tender) = 0 and GetSelectedCount([transaction Number])=0, 0,

IF(SubStringCount(Concat(DISTINCT Tender,','),'Tenders Board B') = 0, 0,

IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tenders Board B'}>}DISTINCT Name, '|')&'|'),'|LING|'),1,0)))

=IF(GetSelectedCount(Tender) = 0 and GetSelectedCount([transaction Number])=0, 0,

IF(SubStringCount(Concat(DISTINCT Tender,','),'Tender Board C') = 0, 0,

IF(SubStringCount('|'&Upper(Concat({<Tender = {'Tender Board C'}>}DISTINCT Name, '|')&'|'),'|ANNIE|') and SubStringCount('|'&Upper(Concat({<Tender = {'Tender Board C'}>}DISTINCT Name, '|')&'|'),'|STEPH|'),1,0)))

Re: Possible Multiple If condition

Anonymous — Thu, 01 Dec 2016 01:19:26 GMT

Box A and B now does not appear at all.

What does '|' means?

Re: Possible Multiple If condition

MK_QSL — Thu, 01 Dec 2016 08:55:41 GMT

It's working...as per this..

1) If nothing is selected.. all box should be hidden

2) If Tender Board A (Tender) Selected or Transaction Number 1 or 8 Selected.. BOX A should appear if you have name LING, otherwise it will not appear

3) If Tender Board B (Tender) or Transaction Number 2,3 or 4 selected.. BOX B should come if you have LINK name..

4) If Tender Board C (Tender) or Transaction Number 5,6 or 8 selected, BOX C should come if name are STEPH and ANNIE.

Hope you are looking for this only..

Re: Possible Multiple If condition

Anonymous — Thu, 01 Dec 2016 09:18:01 GMT

Hi,

I Attach my qvw and data here. Not sure why it is not working for me.

Yes, you are right. So base on the data. Transaction Number 2&4 only Box B will be visible and Transaction number 6 & 7 only Box C is visible, the rest of the transaction number should have the boxes hidden.

Re: Possible Multiple If condition

MK_QSL — Thu, 01 Dec 2016 09:19:16 GMT

Provide your application here..

Re: Possible Multiple If condition

Anonymous — Fri, 02 Dec 2016 08:46:02 GMT

Hi, I have attached the qvw and data, The conditional expression is the same as you scripted, but I did not work for me.

Re: Possible Multiple If condition

MK_QSL — Fri, 02 Dec 2016 09:31:23 GMT

First of all you haven't used below..

CrossTable(NO, Name,2)

LOAD [transaction Number],

Tender,

[Name 1],

[Name 2],

[Name 3]

FROM

[Example tessting.xlsx]

(ooxml, embedded labels, table is Sheet1);

1) You need to reload the script which will convert your table name1, name2 and name3 to NO and Name

2) Now you can use my above expressions. It will work.