topic Re: Pick and Match in QlikView

Pick and Match

markgraham123 — Mon, 27 Jul 2015 21:43:34 GMT

Hi,

I have a requirement which is as follows:

I have Warehouse, ID, Units

Where Warehouse comes from Table1 and ID and Units were from Table2.

When i select Warehouse from Table1, Count(Distinct(ID) from Table 2and Sum(Units) from Table2 should be displaye d in table.

=Pick(Match([W1], 'ABC'), Sum(TOTAL{<W2= {'ABC'}>} [Units]))

=Pick(Match([W1], 'ABC'), Count(Distinct{<W2= {'ABC'}>} ID))

The above coed is not giving me right answer...

Can anyone look into this.

Re: Pick and Match

MarcoWedel — Mon, 27 Jul 2015 22:36:04 GMT

please post sample app/data

thanks

regards

Marco

Re: Pick and Match

swuehl — Mon, 27 Jul 2015 22:41:56 GMT

What do you mean with 'not giving right answer'?

If you don't tell us by, it's just guessing around.

And what is field W1, maybe the Warehouse field?

If you select more then a single W1 value or you don't have any selection on this field, Match([W1],'ABC') will obviously not work even when 'ABC' is part of the possible values.

It's most of the time easier to help you if you could post a small sample QVW, or a sample INLINE data set and your expected result.

Re: Pick and Match

nagaiank — Tue, 28 Jul 2015 01:29:03 GMT

Try the following expressions instead:

=Pick(Match([W1], 'ABC')+1, Null(),Sum(TOTAL{<W2= {'ABC'}>} [Units]))

=Pick(Match([W1], 'ABC')+1, Null(),Count(Distinct{<W2= {'ABC'}>} ID))

Re: Pick and Match

markgraham123 — Tue, 28 Jul 2015 01:34:02 GMT

Hi,

Here is the sample data.

Re: Pick and Match

markgraham123 — Tue, 28 Jul 2015 01:34:41 GMT

Hi Sir,

I tried it.

is =Pick(Match([W1], 'ABC')+1, Null(),Sum(TOTAL{<W2= {'ABC'}>} [Units]))

same as

=Pick(Match([W1], 'a'), Sum(TOTAL{<W2= {'a'}>} [units])) ???

Can u pls lemme knw about the logic??

Re: Pick and Match

jagan — Tue, 28 Jul 2015 02:35:03 GMT

Hi,

Instead of joining you can use Concatenate() like below

Table:

LOAD ID,

W1 AS Warehouse,

units,

'Table1' AS Source

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Concatenate(Table)

LOAD ID,

W2 AS Warehouse,

units,

'Table2' AS Source

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

Now W1 and W2 are in single field and values are summed up when selected in this field.

To get Count and Sum of values use below expressions

Count({<Source={'Table2'}>}Distinct ID)

Sum({<Source={'Table2'}>}Distinct Units)

Regards,

Jagan.

Re: Pick and Match

nagaiank — Tue, 28 Jul 2015 02:49:45 GMT

Match([W1], 'ABC') returns zero if W1 is not equal to ABC.

pick(n, expr1[ , expr2,...exprN])

Returns the n:th expression in the list. n is an integer between 1 and N.

So in order to make the first number 1, I am adding 1 to the result of Match().

Re: Pick and Match

markgraham123 — Tue, 28 Jul 2015 21:20:06 GMT

Hi Sir,

Here is the application...

Sum is working but not Count....

Re: Pick and Match

markgraham123 — Tue, 28 Jul 2015 21:33:13 GMT

using the same pick and match functions, can i please know how can i diaply the values of LBCD.

=Pick(Match([W1], 'OB1')+1, Null(),Count(Distinct{<[W2]= {'OB1'}>} LBCD))

Re: Pick and Match

swuehl — Tue, 28 Jul 2015 21:37:42 GMT

What do you expect to see?

I do see a correct value returned (1)

W1	=Pick(Match([W1], 'a')+1, Null(),Count(Distinct{<W2= {'a'}>} LBCD))	sum(Qty)
		4601
a	1	234
b		4323
c		32
d		12

Re: Pick and Match

markgraham123 — Tue, 28 Jul 2015 21:44:31 GMT

Hi,

i.e. in the above report it is showing Count(Distinct(LBCD)) is 1.

I wanna see the LBCD.."4545" in the report.

Re: Pick and Match

swuehl — Tue, 28 Jul 2015 21:51:16 GMT

A count() will just count the field values, not showing them. Use only() or concat() instead.

And since you are using a set analysis filter like {<W2 = {'a'}>}, the returned LBCD will be 22, not 4545.

It's quite unclear what you are trying to achieve.

Maybe it's better for us to help you if you start explaining your requirements.

Re: Pick and Match

markgraham123 — Tue, 28 Jul 2015 21:59:16 GMT

Hi,

For cross checking purposes whether the Set analysis count i'm using working correct or not...

I wanna display the LBCD...

For eg:

he above report it is showing Count(Distinct(LBCD)) is 1.

I wanna see the LBCD..its fetching is "4545" for my own reference (not in the original report).

Re: Pick and Match

markgraham123 — Wed, 29 Jul 2015 00:26:53 GMT

Hi Sir,

Pls lemme know is there any option to look the fetching value.