Posts Tagged ‘ Match() ’

Qlikview: Section Access and Sheet Access by region.

Here is a quick example.

USER1 can access Region: USA, USER2 can access Region: CANADA, USER3 can access Region: EUROPE, USER4 can access Region: ASIA and USA, USER5 can access All region.
You can change REGION to AccessLevel or TabName or whatever you need or want to call it.

Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD, REGION
ADMIN, ADMIN, ADMINPASS, *
USER, USER1, USER111, USA
USER, USER2, USER222, CANADA
USER, USER3, USER333, EUROPE
USER, USER4, USER444, ASIA
USER, USER4, USER444, USA
USER, USER5, USER555, *
];
Section Application;
LOAD * INLINE [
REGION
USA
CANADA
EUROPE
ASIA
];

Open up Document Properties and click Opening Tab.
SECTION_ACCESS1

Create 4 Sheets: one per region.
Create two Text object and one list box for REGION.
QVUser() will give you the user name.

Open up Sheet Properties for each sheet and type in following formula in Conditional for Show Sheet. Match() function returns position of corresponding text. Dollar Sign Expansion will be evaluated first and Concat() function will return Available REGION.
i.e. ‘USA’,’CANADA’,’EUROPE’,’ASIA’ or ‘USA’,’ASIA’ or ‘USA’ based on user Access.

QlikView will show the sheet if the result is not zero.

Tab USA: =MATCH(‘USA’, $(=CONCAT(CHR(39)&REGION&CHR(39), ‘,’)))
Tab CANADA: =MATCH(‘CANADA’, $(=CONCAT(CHR(39)&REGION&CHR(39), ‘,’)))
Tab EUROPE: =MATCH(‘EUROPE’, $(=CONCAT(CHR(39)&REGION&CHR(39), ‘,’)))
Tab ASIA: =MATCH(‘ASIA’, $(=CONCAT(CHR(39)&REGION&CHR(39), ‘,’)))

section_access5

section_access2

section_access3

section_access4

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Simple!

Qlikview: Dollar Sign Expansion – used in a function.

In some cases, you want to use a function inside a function.  In that case you need to use the Dollar Sign Expansion.

A_Function($(=Another_Function()))

i.e. Create two identical tables (Fruits and Fruits2) with three fruits in it.
Fruits:
Load * Inline [
Fruit
Apple
Banana
Orange
Grape
];

Fruits2:
Load * Inline [
Fruit2
Apple
Banana
Orange
Grape
];

After reload is done, add two list boxes: Fruit and Fruit2 in the layout.

Then create a Text Box with following Syntax.

=Match(Fruit, $(=Concat(chr(39)&Fruit2&chr(39), ‘,’)))

In the Caption tab, type in following into Title Text.

=’Match(‘&Fruit&’, ‘ &Concat(chr(39)&Fruit2&chr(39), ‘,’) & ‘)’

Then Create a Straight Table with following.

Dimension: Add a calculated dimension = ValueList($(=concat(chr(39)&Fruit2&chr(39), ‘,’)))

Expression: Add an expression

=Pick(Match(ValueList($(=concat(chr(39)&Fruits2&chr(39), ‘,’))), $(=concat(chr(39)&Fruits&chr(39), ‘,’))),
‘Pick 1: 1+4=’ & text($(=’1+4′)),
‘Pick 2: Today=’ & Date(Today(), ‘MM/DD/YYYY’),
‘Pick 3: Orange’)

dollar_sign_expansion1

dollar_sign_expansion2

Try to select items in Fruit or Fruit2 and see the results in the Text object and Straight Table.