topic Re: how to write expression in QlikView

how to write expression

kunkumnaveen — Wed, 20 Apr 2016 09:21:38 GMT

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

sunny_talwar — Wed, 20 Apr 2016 09:23:58 GMT

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

swuehl — Wed, 20 Apr 2016 09:25:50 GMT

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

kkkumar82 — Wed, 20 Apr 2016 09:27:31 GMT

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

kunkumnaveen — Wed, 20 Apr 2016 09:29:28 GMT

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

kunkumnaveen — Wed, 20 Apr 2016 09:32:03 GMT

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

Re: how to write expression

sunny_talwar — Wed, 20 Apr 2016 09:33:01 GMT

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

kunkumnaveen — Wed, 20 Apr 2016 09:49:08 GMT

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

PradeepReddy — Wed, 20 Apr 2016 09:50:11 GMT

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

nico_ilog — Wed, 20 Apr 2016 09:51:48 GMT

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

Re: how to write expression

swuehl — Wed, 20 Apr 2016 09:55:15 GMT

Use a mapping approach instead of a JOIN:

MAP:

MAPPING

LOAD * INLINE [

code, decision

A , accepted

A0, accepted

L1A, exception

R, rejected

];

LOAD

number, valueold, code,

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

...

FROM

Re: how to write expression

sunny_talwar — Wed, 20 Apr 2016 09:55:36 GMT

Try this may be:

table1:

LOAD *,

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

LOAD * Inline [

number, valueold, code

1000, , A

1001, L1, A

1003, , R

1004, , A0

];

table2:

LOAD *,

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

LOAD * Inline [

code, decision

A, accepted

A0, accepted

A, exception

R, rejected

];

Join (table1)

LOAD *

Resident table2;

DROP Table table2;

Re: how to write expression

sunny_talwar — Wed, 20 Apr 2016 10:01:29 GMT

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

kunkumnaveen — Wed, 20 Apr 2016 10:04:15 GMT

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

kunkumnaveen — Wed, 20 Apr 2016 10:07:13 GMT

Hello, if i got script like

qave:

LOAD PRUEFLOS,

VCODE

FROM

(qvd);

Left JOIN(qave)

CDPOS:

LOAD OBJECTID as PRUEFLOS,

VALUE_OLD

FROM

(qvd);

LEFT JOIN(qave)

LOAD VCODE,

VALUE_OLD,

Decision

FROM

(ooxml, embedded labels, table is Sheet1);

then

Re: how to write expression

kunkumnaveen — Wed, 20 Apr 2016 10:15:26 GMT

Hello, if i got script like

qave:

LOAD PRUEFLOS,

VCODE

FROM

(qvd);

Left JOIN(qave)

CDPOS:

LOAD OBJECTID as PRUEFLOS,

VALUE_OLD

FROM

(qvd);

LEFT JOIN(qave)

LOAD VCODE,

VALUE_OLD,

Decision

FROM

(ooxml, embedded labels, table is Sheet1);

then

how to write expression

Re: how to write expression

swuehl — Wed, 20 Apr 2016 10:19:09 GMT

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		A	accepted
1001	L1	A	exception
1004		A0	accepted
1003		R	rejected

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

kunkumnaveen — Wed, 20 Apr 2016 10:29:50 GMT

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

(qvd);

Left JOIN(qave)

CDPOS:

LOAD OBJECTID as PRUEFLOS, (which is number )

VALUE_OLD (which is valueold)

FROM

(qvd);

LEFT JOIN(qave)

LOAD VCODE, (which is code)

VALUE_OLD, (which is valueold)

Decision

FROM

(ooxml, embedded labels, table is Sheet1);

how to write expression

Re: how to write expression

kunkumnaveen — Wed, 20 Apr 2016 10:50:17 GMT

Hello sunny,

where to use apply map in this script

table1

qave:

LOAD PRUEFLOS, (which is number )

VCODE (which is code)

FROM

(qvd);

Left JOIN(qave)

CDPOS:

LOAD OBJECTID as PRUEFLOS, (which is number )

VALUE_OLD (which is valueold)

FROM

(qvd);

LEFT JOIN(qave)

LOAD VCODE, (which is code)

VALUE_OLD, (which is valueold)

Decision

FROM

(ooxml, embedded labels, table is Sheet1);

Re: how to write expression

swuehl — Wed, 20 Apr 2016 13:17:38 GMT

Something like this

MAP:

MAPPING LOAD

VALUE_OLD&VCODE, Decision;

LOAD VCODE, //(which is code)

VALUE_OLD, // (which is valueold)

Decision

FROM

(ooxml, embedded labels, table is Sheet1);

qave:

LOAD PRUEFLOS, //(which is number )

VCODE // (which is code)

FROM

(qvd);

Left JOIN(qave)

CDPOS:

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

VALUE_OLD // (which is valueold)

FROM

(qvd);

RESULT:

LOAD *,

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

RESIDENT qave;

DROP TABLE qave;