How to compare two fields/columns in expression in Straight Table?

[benvatvandata]

Hello,

I have a straight table that displays:

[Owning Customer Code], [Item Code], [Customer Item Reference], and the Sum(ReservedQty + FreeQty) for each ItemCode. (We do not want to display [Item Customer Code])

I am trying to compare two fields ([Owning Customer Code] and [Item Customer Code]) in an expression. I want to display MaxString([Customer Item Reference]) where [Owning Customer Code] = [Item Customer Code] for each ItemCode. Originally I made an expression that seemed to work ok, but later found out that some [Customer Item References] were not being displayed correctly for a few ItemCodes where [Owning Customer Code] = [Item Customer Code].

The original expression is: "=IF( [Item Customer Code] = [Owning Customer Code], MaxString([Customer Item Reference]), ' ')

After finding out about this issue I've attempted to create a different expression that would solve this problem, however, now it only displays correctly when a specific [Owning Customer Code] is selected, otherwise no values are displayed.

The new expression is: "=Aggr( MaxString( {< [Item Customer Code] = [Owning Customer Code] >} [Customer Item Reference]), [Owning Customer Code], [Item Code])

Is there a way to compare these fields by each row so that a selection is not needed?

Background Information:

Each [Owning Customer Code] can have multiple ItemCodes, multiple ItemCustomerCodes for each ItemCode, and multiple CustomerItemReferences for each ItemCustomerCode. Adding onto that, each ItemCustomerCode can have multiple ItemCodes and OwningCustomerCodes. I've attached screen snips of the tables showing this.

Thanks,

Ben V

[sunny_talwar]

How about this:

MaxString({<[Item Code] = {"=[Item Customer Code] = [Owning Customer Code]">} [Customer Item Reference])

MaxString(Aggr(If([Item Customer Code] = [Owning Customer Code],  [Customer Item Reference]), [Owning Customer Code], [Item Code]))

or

Create a new field in the script:

LOAD AutoNumber([Owning Customer Code]&[Item Code]) as NewField

and then use it like this:

MaxString({<NewField = {"=[Item Customer Code] = [Owning Customer Code]">} [Customer Item Reference])

[Digvijay_Singh]

See if calculated dimension works here for Owning Customer Code-

If([Item Customer Code] = [Owning Customer Code],[Owning Customer Code])

then

MaxString([Customer Item Reference]) may work as an expression.

[benvatvandata]

MaxString({<[Item Code] = {"=[Item Customer Code] = [Owning Customer Code]">} [Customer Item Reference])

MaxString(Aggr(If([Item Customer Code] = [Owning Customer Code],  [Customer Item Reference]), [Owning Customer Code], [Item Code]))

Thanks for the quick response!

These both work for the most part, but I am still finding rows that are not displaying a [Customer Item Reference] when they should be.

For example, in my attachment "QlikView example for report issue":

[Owning Customer Code]  = 1100600, [Item Code] = 2134669, [Item Customer Code] = 1100600...

[Customer Item Reference] should display 0111006.85-25, but it does not in QlikView.

I had thought about doing this in the script, but I'm requested to avoid making any script changes when possible.

Any other ideas?

[benvatvandata]

See if calculated dimension works here for Owning Customer Code-

If([Item Customer Code] = [Owning Customer Code],[Owning Customer Code])

then

MaxString([Customer Item Reference]) may work as an expression.

The issue with this is that I still need to display [Owning Customer Code] and it's ItemCodes where [Item Customer Code] is not equal to [Owning Customer Code].

I'm trying to display all of the [Owning Customer Code]'s ItemCodes (sometimes a [Item Customer Code] will be the same as the [Owning Customer Code], most of the time it will not), but I only want to display [Customer Item Reference] when those two fields are equal.

[sunny_talwar]

Would you be able to share a sample? Will become very easy to troubleshoot

[benvatvandata]

Yes, there are a few more columns and extras in the expressions in the real chart, it's difficult to explain the complexity so I was just trying to keep it a little simpler. (I'll attach screen clip of real chart)

These are all of my expressions:

Customer Item Reference (this is the expression that only works when a Owning Customer Code is selected):

=AGGR(MaxString({< [Item Customer Code] = [Owning Customer Code],

  [Owning Customer Code] -= {''},

  [Item Analysis Type] = {'2 Bundle Size'},

  [Item Inactive Code] = {0} >} [Customer Item Reference]),  [Owning Customer Code], [Item Code])

Customer Item Reference (this is one of your expression examples, I tried to revise it to make it work like the other expressions):

MaxString({< [Owning Customer Code] -= {''},  [Item Analysis Type] = {'2 Bundle Size'},

  [Item Inactive Code] = {0} >} AGGR(If([Item Customer Code] = [Owning Customer Code], [Customer Item Reference]),  [Owning Customer Code], [Item Code]))

OnHand Qty:

=Sum( {< [Owning Customer Code] -= {''}, LinkId = {'INVHST'}, [Item Inactive Code] = {0}, [Item Analysis Type] = {'2 Bundle Size'}  >} [Inventory Free Quantity] + [Inventory Reserved Quantity])

Prod Qty expression:

Sum( {< [Owning Customer Code] -= {''}, LinkId = {'Job'}, [Item Inactive Code] = {0},

[Item Analysis Type] = {'2 Bundle Size'} >} [JobLine Ordered Quantity])

Ordered Qty expression:

Sum( {< [Owning Customer Code] -= {''}, LinkId = {'SAL'}, [Sales Source Code] -= {'PLAN'}, [Sales Order Status Code] -= {'9'},   [Item Inactive Code] = {0}, [Item Analysis Type] = {'2 Bundle Size'}  >} [Sales Ordered Quantity])

Bundle Size expression:

Aggr(Only({<[Owning Customer Code] -= {''}, [Item Analysis Type] = {'2 Bundle Size'},

[Item Inactive Code] = {0} >} [Item Analysis Code]),[Owning Customer Code],[Item Code])

This is what my chart looks like right now(using my expressions) when Owning Customer Code 1100600 is selected:

This is what it looks like when I changed my expression with your example(however when a selection is not made, yours shows some Customer Item References, but it is still missing the one that shows up in my above image):

Sorry for the late response, any help is appreciated!

[benvatvandata]

I think I got it to work now... I switched [Item Code] with [Customer Item Reference] in your first formula. Thanks for the help!

= MaxString({<[Item Analysis Type] = {'2 Bundle Size'}, [Item Inactive Code] = {0}, [Owning Customer Code] -= {''},

[Customer Item Reference] = {"=[ItemCustCode] = [ItemUDE ItemUDE: OwningCustomer]"} >} [Customer Item Reference])

[benvatvandata]

Correction: I thought the new formula I created from your examples worked great (it did for the most part), but noticed some Customer Item References missing for a few ItemCodes...

Not sure if you're interested in this, but with me being a novice to all this, I have to say I am a little proud of myself for figuring this one out . (I was having trouble getting the max of CustItemRef because some values were integers and some were strings... This seemed to solve that problem):

[Customer Item Reference] expression:

=If(Isnull(MaxString({< [Owning Customer Code] -= {''} , [Item Inactive Code] = {0} >}

(If(isnull(Pick(Match([Owning Customer Code], [Item Customer Code]),[Item Customer Code])),null(),

[Customer Item Reference])))),

Max({< [Owning Customer Code] -= {''} , [Item Inactive Code] = {0} >}

(If(isnull(Pick(Match([Owning Customer Code], [Item Customer Code]),[Item Customer Code])),null(),

[Customer Item Reference]))),

MaxString({< [Owning Customer Code] -= {''} , [Item Inactive Code] = {0} >}

(If(isnull(Pick(Match([Owning Customer Code], [Item Customer Code]),[Item Customer Code])),null(),

[Customer Item Reference]))))

It's pretty messy, but it did the trick!

Any advice on simplifying this is greatly appreciated!