topic Re: Set Analysis in QlikView

Set Analysis

Tue, 14 Jul 2015 14:52:34 GMT

I have a question. I have a table with material ID, PO, category code and PO value. For one PO, there maybe various material ID and category codes. Now I would like to make a report with Cagegory code and PO values in which one PO maps to the PCS with biggest value in the PO.

Re: Set Analysis

robert_mika — Tue, 14 Jul 2015 15:44:37 GMT

Could you post sample of your data?

Re: Set Analysis

Tue, 14 Jul 2015 19:37:18 GMT

Thank you, Robert. Here is the sample of the data. Is it possible to achieve it through Set Analysis?

Re: Set Analysis

sunny_talwar — Tue, 14 Jul 2015 20:02:13 GMT

Please post an excel file instead of a screenshot.

Best,

Sunny

Re: Set Analysis

saurabh5 — Tue, 14 Jul 2015 20:29:03 GMT

hi Alan,

you can create a straight table and select dimension as PO and category type, and expression as

=aggr( sum(Value), PO).

you would get the expected result.

Thanks

Saurabh

Re: Set Analysis

Tue, 14 Jul 2015 22:54:09 GMT

Dear Sunny,

I can not find where to post a excel file. I only have the options of image or link. Would you please let me know how to post a excel file. Thanks.

Regards,

Alan

Re: Set Analysis

Tue, 14 Jul 2015 22:59:13 GMT

Dear Sunny, please try this link. Thanks.https://community.qlik.com/docs/DOC-8897

Re: Set Analysis

Tue, 14 Jul 2015 23:19:14 GMT

Hi Saurabh,

Many thanks for your help. I appreciate it very much.

There is still one thing pending here. I am trying to recognize the category code with highest amount in the PO and use it to map each PO. Any idea how to do it?

Thanks.

Re: Set Analysis

sunny_talwar — Wed, 15 Jul 2015 00:37:41 GMT

Create a flag in the script:

Table:

LOAD PO,

[Material ID],

[Category Code],

Value

FROM

Community_172509.xlsx

(ooxml, embedded labels, table is Sheet1);

Join(Table)

LOAD PO,

FirstValue([Category Code]) as [Category Code],

1 as Flag

Resident Table

Group By PO;

Output in straight table (to see the flag)

Now to get your expected output, create a straight table with

Dimension: PO

Expressions:

1. =Only({<Flag = {1}>} [Category Code])

2. =Sum(Value)

Output of straight table:

Attaching the qvw document for reference.

HTH

Best,

Sunny

Re: Set Analysis

rwunderlich — Wed, 15 Jul 2015 01:33:54 GMT

I think you can use PO as your first Dimension, and a second calculated dimension to select the largest Category for each PO as:

=if([Category Code]=

aggr(

FirstSortedValue([Category Code],

-aggr(sum(Value), [Category Code])

)

,PO)

,[Category Code])

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Set Analysis

Mon, 27 Jul 2015 21:22:15 GMT

Dear all,

Many thanks for your kindly supports and help. The problem is perfectly solved. Much appreciated.

Thanks.

Regards,

Alan