19 Replies Latest reply: Apr 20, 2016 9:19 AM by Stefan Wühl

# how to write expression

Hello, if i got a sample like this

number         code           value old                decision               category                date

1000                A                                           accepted                    smelting              01/01/2015

1001                A                   L1                     exception                     smelting               01/01/2015

1003                A                    L2                    exception                    smelting             01/01/2015

1004                A0                                           accepted                  smelting              01/01/2015

1005                 R                                           rejected                    smelting                01/01/2015

in a bar chart if i use

Dimension:      category,decision

expresssion:  ????????????? what expression do i need to write

so that to get this o/p

X-axis in bar chart                         count=        2                   1                        2

accepted       rejected              exception

expression:i wrote  this ......but the problem is how can i make this A code numbers in to two diff decision count

=count({\$<Category={'smelting'},ROLE=,SUPCAT=,MaterialName=,YEAR=,QUATERS=,name1=,date={">=\$(vFromdate)<=\$(vTodate)"}>}number)

• ###### Re: how to write expression

naveen kumar wrote:

expression:i wrote  this ......but the problem is how can i make this A code numbers in to two diff decision count

What do you mean when you say two diff decision count?

• ###### Re: how to write expression

When you are giving dates from and to also input in set analysis how can get the count as you expected Naveen

• ###### Re: how to write expression

as far as my known ledge based up on expression the count changes dynamic

• ###### Re: how to write expression

HI,

I mean if you see code A as two decision

1)accepted

2)exception

so i want differentiate the numbers,

like code=A       count =1 for accepted

code=A      count =2 for exception

• ###### Re: how to write expression

But code is not even one of your dimension. I think you should get 2, 1, 2 as the result of your expression. Are you not getting it?

• ###### Re: how to write expression

i think i am completely wrong with  what i said

Actually the problem is ...

if i got data like this

table1:

number     valueold             code

1000                                     A

1001           L1                      A

1003                                     R

1004                                     A0

table2:

code                decision

A                      accepted

A0                      accepted

A                          exception

R                         rejected

so if i join these two i am like

number                code               decision

1000                        A                  accepted

1000                         A                  exception

1001                           A             accepted

1001                          A                 excception

1003                            R                    rejected

1004                            A0                   accepted

which  is wrong

i want like this

number                   code                   decision

1000                        A                  accepted

1001                          A                 excception

1003                            R                    rejected

1004                            A0                   accepted

these that valueold field based up on that i need to differentiate the codes and there numbers

but i am not sure whether to differentiate it at script or at expression level

• ###### Re: how to write expression

Use a mapping approach instead of a JOIN:

MAP:

MAPPING

code,                decision

A ,                     accepted

A0,                      accepted

L1A,                          exception

R,                         rejected

];

number,     valueold,             code,

ApplyMap('MAP', valueold&code, 'no Map') as decision

...

FROM

• ###### Re: how to write expression

To elaborate a bit more ,

while checking each product(number) the end user gives the rating (code)

i mean some products which were directly accepted (code=A)

and some products which were not accepted first(valueold =L1) then a later if same product get accepted  which comes under exception accepted(code =A)...so the client want to see the count which were directly accepted and which were indirectly accepted  i mean (exception count)

• ###### Re: how to write expression

Naveen Kumar,

I am not sure what you want to tell me. That the mapping approach doesn't work?

I do get this with my code posted above:

number valueold code decision
1000 Aaccepted
1001L1Aexception
1004 A0accepted
1003 Rrejected

If it doesn't work with your real data, then please take a little time to prepare a more complete sample application (that should be reloadable) and attach this to your thread.

Please consider that also (our) time is a limited ressource.

• ###### Re: how to write expression

exactly,  i am looking for the same o/p ....but only the thing is i got three diff  tables and i am not sure on which table do i need to use this apply map  ,i got struck at that plz will you suggest me ...

table1

qave:

LOAD     PRUEFLOS,  (which is number )

VCODE            (which is code)

FROM

[C:\qlikview\QVDS\Decision.qvd]

(qvd);

Left JOIN(qave)

CDPOS:

LOAD           OBJECTID as PRUEFLOS,            (which is number )

VALUE_OLD                                  (which is valueold)

FROM

[C:\qlikview\QVDS\CDPOS.qvd]

(qvd);

LEFT JOIN(qave)

VALUE_OLD,                               (which is valueold)

Decision

FROM

[C:\qlikview\QVDS\VCodeMapping.xlsx]

(ooxml, embedded labels, table is Sheet1);

• ###### Re: how to write expression

Something like this

MAP:

VALUE_OLD&VCODE, Decision;

VALUE_OLD,                            //  (which is valueold)

Decision

FROM

[C:\qlikview\QVDS\VCodeMapping.xlsx]

(ooxml, embedded labels, table is Sheet1);

qave:

LOAD    PRUEFLOS,  //(which is number )

VCODE        //  (which is code)

FROM

[C:\qlikview\QVDS\Decision.qvd]

(qvd);

Left JOIN(qave)

CDPOS:

LOAD          OBJECTID as PRUEFLOS,            //(which is number )

VALUE_OLD                                //  (which is valueold)

FROM

[C:\qlikview\QVDS\CDPOS.qvd]

(qvd);

RESULT:

ApplyMap('MAP', VALUE_OLD & VCODE, 'No Mapping found') as Decision

RESIDENT qave;

DROP TABLE qave;

• ###### Re: how to write expression

Hello, if i got script like

qave:

VCODE

FROM

[C:\qlikview\QVDS\Decision.qvd]

(qvd);

Left JOIN(qave)

CDPOS:

VALUE_OLD

FROM

[C:\qlikview\QVDS\CDPOS.qvd]

(qvd);

LEFT JOIN(qave)

VALUE_OLD,

Decision

FROM

[C:\qlikview\QVDS\VCodeMapping.xlsx]

(ooxml, embedded labels, table is Sheet1);

then

• ###### Re: how to write expression

Try this may be:

table1:

AutoNumber(number, code&'1') as Key;

number,    valueold,            code

1000,                ,                    A

1001,          L1,                      A

1003,              ,                      R

1004,              ,                      A0

];

table2:

AutoNumber(decision, code&'2') as Key;

code,                decision

A,                      accepted

A0,                      accepted

A,                          exception

R,                        rejected

];

Join (table1)

Resident table2;

DROP Table table2;

• ###### Re: how to write expression

I guess, I did not read through. Although in this particular case my script might be working, but Stefan's script is more scale-able as it meets the requirement of oldvalue

• ###### Re: how to write expression

Hello, if i got script like

qave:

VCODE

FROM

[C:\qlikview\QVDS\Decision.qvd]

(qvd);

Left JOIN(qave)

CDPOS:

VALUE_OLD

FROM

[C:\qlikview\QVDS\CDPOS.qvd]

(qvd);

LEFT JOIN(qave)

VALUE_OLD,

Decision

FROM

[C:\qlikview\QVDS\VCodeMapping.xlsx]

(ooxml, embedded labels, table is Sheet1);

then

• ###### Re: how to write expression

Hello sunny,

where to use apply map in this script

table1

qave:

LOAD     PRUEFLOS,  (which is number )

VCODE            (which is code)

FROM

[C:\qlikview\QVDS\Decision.qvd]

(qvd);

Left JOIN(qave)

CDPOS:

LOAD           OBJECTID as PRUEFLOS,            (which is number )

VALUE_OLD                                  (which is valueold)

FROM

[C:\qlikview\QVDS\CDPOS.qvd]

(qvd);

LEFT JOIN(qave)

VALUE_OLD,                               (which is valueold)

Decision

FROM

[C:\qlikview\QVDS\VCodeMapping.xlsx]

(ooxml, embedded labels, table is Sheet1);

• ###### Re: how to write expression

I agree, I don't understand completely. You have one Count() expression, so maybe

=count({\$<Category={'smelting'},ROLE=,SUPCAT=,MaterialName=,YEAR=,QUATERS=,name1=,date={">=\$(vFromdate)<=\$(vTodate)"}, code = {A} >}number)

• ###### Re: how to write expression

edit: insert the image

for the bellow dimension & expressions, you will get the result as you expected..

Dimensions: category,decision

Expression: Count(number)

• ###### Re: how to write expression

Naveem,

Your Set analysis should work as explained by the other community members.

My logic would be:

The graph's dimension = CODE

The expression1 [Exception Items] =count({\$<Category={'smelting'},decision = {'exception'}>}number)

The expression2 [Accepted Items] =count({\$<Category={'smelting'},decision = {'accepted'}>}number)

The expression3 [Rejected Items] =count({\$<Category={'smelting'},decision = {'rejected'}>}number)

Leave out the date={">=\$(vFromdate)<=\$(vTodate)" for now, because if your date formats aren't the same in the Variables and in the tables then you will get null values anyway. Check to see if you get the desired results then build in the dates.

Just a FYI: Hard-coding the scenario into the graph with SET ANALYSIS, is not always ideal. If the data structure changes or more CODES | DECISIONS are added into the data, then they will be neglected, because these have been hard-coded.

IF you can't win, share the QVW into the thread, i'm sure we'll figure it out.

Nico