Re: how to write expression - Page 2 - Qlik Community

kunkumnaveen · ‎2016-04-20

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)

swuehl · ‎2016-04-20

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

sunny_talwar · ‎2016-04-20

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;

sunny_talwar · ‎2016-04-20

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

kunkumnaveen · ‎2016-04-20

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)

kunkumnaveen · ‎2016-04-20

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

kunkumnaveen · ‎2016-04-20

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

swuehl · ‎2016-04-20

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.

kunkumnaveen · ‎2016-04-20

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

kunkumnaveen · ‎2016-04-20

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);

swuehl · ‎2016-04-20

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;