topic Re: Set Analysis where Field A = Field B query in QlikView

Set Analysis where Field A = Field B query

Thu, 30 Aug 2012 15:49:06 GMT

Hi Guys,

I'm trying to create a straight table using Set analsis where it sums the quantity based on whether the Supplier Code field is equal to the Prime Supplier Field, the Secondary Supplier Field or Tertiary Supplier Field.

To do this i've been using the below Expression;

Expression Title Expression

Prime Supplier Qty = Sum({$<[Supplier Code]={'=[Supplier Code]=[Prime Supplier]'}>} [Orig. Ordered Qty])

Secondary Supplier Qty = Sum({$<[Supplier Code]={'=[Supplier Code]=[Secondary Supplier]'}>} [Orig. Ordered Qty])

Tertiary Supplier Qty = Sum({$<[Supplier Code]={'=[Supplier Code]=[Tertiary Supplier]'}>} [Orig. Ordered Qty])

However, it doesn't seem to be working correctly;

If also written the expression without set analysis (See right column) and it seems to work so can someone tell me what i'm doing wrong?

Regards,

Nick

Re: Set Analysis where Field A = Field B query

whiteline — Thu, 30 Aug 2012 16:16:50 GMT

Try to use " instead '.

=Sum({$<[Supplier Code]={"=[Supplier Code]=[Prime Supplier]"}>} [Orig. Ordered Qty])

Re: Set Analysis where Field A = Field B query

Anonymous — Thu, 30 Aug 2012 16:18:41 GMT

Nick,

When I started reading, wanted to suggest the solution without set analysis - but later saw that you had it already. This is the correct way in this case. Set analysis does not evaluate data per dimension, hence it cannot be used in cases like this.

Regards,

Michael

Re: Set Analysis where Field A = Field B query

whiteline — Thu, 30 Aug 2012 16:24:26 GMT

There is no need to "evaluate data per dimension". These set expressions uses search capabilities to reduce the set with only the values where [Supplier Code]=[Prime Supplier].

Then Sum function is used over this set in a chart with dimension.

Why it can't be used in a such way ?

Re: Set Analysis where Field A = Field B query

Anonymous — Thu, 30 Aug 2012 16:45:55 GMT

In general, set analysis syntax [Supplier Code]=[Prime Supplier] is fine. Or [Supplier Code]=P([Prime Supplier]).

In this case, where [Supplier Code] is a chart's dimension, you'll get the same result in each line because set "doesn't know" anything about charts and dimensions. It evaluates overall data.

Regards,

Michael

Re: Set Analysis where Field A = Field B query

whiteline — Thu, 30 Aug 2012 17:58:36 GMT

Just look at the attachment.

Re: Set Analysis where Field A = Field B query

Anonymous — Thu, 30 Aug 2012 21:51:11 GMT

There is something here... I'll take a look, thanks.

Followup: Tried. I wish I was wrong... The example with set analysis works only if both dimensions are in the same table. Usually it is not the case, and it was - easy to resove in the script as well. So, using "if" is the only solution.

Re: Set Analysis where Field A = Field B query

jagan — Fri, 31 Aug 2012 04:47:30 GMT

Hi,

Hope attached file helps you.

Regards,

Jagan.

Re: Set Analysis where Field A = Field B query

Fri, 31 Aug 2012 08:13:41 GMT

Thanks Michael and Whiteline,

I have tried updating the expression to

Sum({$<[Supplier Code]={"=[Supplier Code]=[Prime Supplier]"}>} [Orig. Ordered Qty])

and it's still showing 0.

I'm quite happy to use the Sum(If( expression if that's the correct way? I thought i had read that "Sum IF" had a higher overhead on the processor than set analysis.

Also, using SumIf doesn't seem to TOTAL correctly at the top? (Please see .bmp in original post for example).

I had originally used [Supplier Code]=P([Prime Supplier]) within the expression, however, unless a selection was made, the quantities were totalled across all suppliers. Please see code '000033' below;

This is with no selection made;

This is with the item code selected;

Note that the right column (Secondary Supplier) changes from 82 to 0 when the item code is selected...

Re: Set Analysis where Field A = Field B query

Fri, 31 Aug 2012 08:18:19 GMT

I'm assuming this is because p() analyses the selection, hence why i wanted to change the expression to not use P().

I also tried "Sum({$<[Supplier Code]=[Prime Supplier]>} [Orig. Ordered Qty])" but again, it evaluates to 0.

Re: Set Analysis where Field A = Field B query

Fri, 31 Aug 2012 12:21:30 GMT

I've been trying to figure out how to do this but i'm clearly missing a field within the expression. I need to include the field [Item Code] in expression somewhere so that it can identify the correct Primary and Secondary Supplier.

What i'm trying to achieve overall is the below;

So that we can identify the quantities and spend by Item from each supplier.

I'm still using the set analysis expression;

sum({$<[Supplier Code]={"=[Supplier Code]=<[Item Code]>[Prime Supplier]"}>}[Orig. Ordered Qty])

as this works when a single line is selected. However, when no selections are made, the table shows all values as 0 for those set analysis (as shown above).

If i need it to evaluate the Item Code as well, how do i incorporate this into the expression?

Regards,

Nick

Re: Set Analysis where Field A = Field B query

whiteline — Fri, 31 Aug 2012 12:26:18 GMT

It's strange. In a simple example everything works fine. I think there is some problems with nulls and table connections or combination of dimensions in this chart.

You could also try text comparison in sets:

Sum({$<[Supplier Code]={"=text([Supplier Code])=text([Prime Supplier])"}>} [Orig. Ordered Qty])

With "ifs" you can try something like this (it gives correct totals):

=Sum(aggr(if([Supplier Code]=[Prime Supplier], Sum([Orig. Ordered Qty]), null()), [Item Code]))

I' use [Item Code] as the lowest level demension that represents row of table. You could have to correct that.

Re: Set Analysis where Field A = Field B query

Fri, 31 Aug 2012 14:29:59 GMT

Thanks Whiteline,

I've attached the file so you can see what i mean.

With no selections made, the Qty columns for Prime, Secondary and Tertiary are shown as blank (not always though, as is the case with 000301).

Once you select an item code, those columns populate with the correct figures.

I can only assume that this is because when no selections are made, the Set Analysis cannot determine the item code and therefore cannot determine who the Prime, Secondary and Tertiary Suppliers are.

Does anyone have any ideas on this?

Regards,

Nick

Re: Set Analysis where Field A = Field B query

whiteline — Fri, 31 Aug 2012 16:24:57 GMT

I think, I've found it.

[Supplier Code] and [Prime Supplier] are fields from different tables connected by Item Code.

As you can see on the attached screen (tablebox) the relations between them are ambigous

(there are many paths through Item Code except 000301 and supplier code 101883).

So I think such logic of sets unfortunately is not applicable in this case.

Re: Set Analysis where Field A = Field B query

Anonymous — Sat, 01 Sep 2012 00:19:34 GMT

Agree, set analysis is not applicable here. Looks like it may work in situation when all fields in set are in the same table.

Regards,

Michael

Re: Set Analysis where Field A = Field B query

whiteline — Sun, 02 Sep 2012 19:00:41 GMT

I think your problem could be solved if there was a way to overcome the ambiguity.

May be, some id field that distinguish one shipmentfact from another could help.

You can then use aggr() explicitly.

But may be, it's time for refactoring the data model...

Re: Set Analysis where Field A = Field B query

Mon, 03 Sep 2012 08:59:53 GMT

Could someone explain why they need to be in the same field for set analysis to work as the relationship between the tables is one to many? I don't doubt that you're right, i just don't undertsand why.

To explain further; each item code can only have one Prime Supplier, one Secondary Supplier and one Tertiary Supplier. However, each item code may have been purchased from any supplier code.

If the relationship is one to many, regardless of who the items were purchased through, any item code will alway have the same Prime, Secondary and Tertiary Supplier. Doesn't this solve the ambiguity or am i missing something?

Thanks for your help with this, I hope this doesn't come across as ungrateful as that is not my intention. I just want to understand why this isn't working.

Regards,

Nick

Re: Set Analysis where Field A = Field B query

Mon, 03 Sep 2012 09:01:13 GMT

To explain further; each item code can only have one Prime Supplier, one Secondary Supplier and one Tertiary Supplier. However, each item code may have been purchased from any supplier code.

Thanks for your help with this, I hope this doesn't come across as ungrateful as that is not my intention. I just want to understand why this isn't working.

Regards,

Nick

Re: Set Analysis where Field A = Field B query

Mon, 03 Sep 2012 09:38:43 GMT

I've joined the tables into one and have checked that there is nothing abnormal about the join. Each Item Code only has one Prime, Secondary and Tertiary Supplier.

It's still not working though...?

Please find enclosed latest file.

Re: Set Analysis where Field A = Field B query

whiteline — Mon, 03 Sep 2012 10:14:51 GMT

The question in not about joining, i think.

Could you add some id field (for example rowno()) as I've mentioned before ?

Becaue the Qty expression works if I use something like this:

sum({$<[Due Date]={"=[Supplier Code]=[Prime Supplier]"}>}[Orig. Ordered Qty])

With [Supplier Code]={"=[Supplier Code]=[Prime Supplier]"} you just CANN'T select all records that have [Supplier Code]=[Prime Supplier] because there both situations:

[Supplier Code]=[Prime Supplier] and [Supplier Code]<>[Prime Supplier] with the same [Supplier Code].

[Due Date] in your simplified case distinguishes these situations.