Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If nothing is selected... what you want?
All three box should appear?
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).
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.
Use below conditions for
A
=
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)))
B
=
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)))
C
=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)))
Box A and B now does not appear at all.
What does '|' means?
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..
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.
Provide your application here..
Hi, I have attached the qvw and data, The conditional expression is the same as you scripted, but I did not work for me.
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.