topic Re: Compare to values and show both Matchfound as Yes in App Development

Compare to values and show both Matchfound as Yes

Daniel1908 — Thu, 27 May 2021 15:31:46 GMT

Hello Together,

Currently I compare 2 Values from different rows.
I count the Material Number if the Text and the Price are equal to each other.

No I want to show both Matchfound as "Yes"

I am using the below in script:

Left Join (Data)

Load
Text,

If(Count(Text)>1,1) as CountText

Resident Data

Group by Text;

Left Join (Data)

Load
"Price",

If(Count("Price")>1,1) as CountPrice

Resident Data

Group by "Price";

Join (Data)

Load

"Price","Text","Materialnumber",
IF ("Price"= Previous("Price") and Text = Previous(Text),'Yes','No') as MatchFound

Resident Data

Order by "Price","Text","Materialnumber" asc;

Join (Data)Load

"Price","Text","Materialnumber",
IF ("Price"= Previous("Price") and Text = Previous(Text),'Yes','No') as MatchFound

Resident Data

Order by "Price","Text","Materialnumber" desc;

Re: Compare to values and show both Matchfound as Yes

Sammy_AK — Fri, 28 May 2021 07:47:23 GMT

i would like to understand the match and group by logic should be implemented at script level? can you not do at UI level?

Re: Compare to values and show both Matchfound as Yes

Daniel1908 — Fri, 28 May 2021 15:11:48 GMT

I would like to display/filter the materials which have the same Price and the same Text. Not only one of both.
First the Group by was set to compare equal Price and equal Text, but if I filtered the equal Price, it showed the ones with different text as well.

Therefore the Matchfound was set.

The Matchfound works, but I can't filter the equal materials because it only shows one as "Yes".
Even if there are more equal materials.

Do you know how to do at UI Level?

Re: Compare to values and show both Matchfound as Yes

Sammy_AK — Mon, 31 May 2021 09:17:42 GMT

yes, there are several ways to achieve this, i able to find a solution using the load script. in order to replicate the similar scenario,

i used a inline load to load the data (please interpret txt as text, mat_num as material number, price as Price and CountPrice as c_prc) and then used a precedence load and derived a column "txt&price" because as mention by you both txt and price should be equal and then be counted.

in the next load, i used "txt&price" as my key and performed a count on price and aggregated by text and price, this will give me the output as required.

in the third using the c_prc, i derived a logic that if the count is greater than 1 then its a yes else no which gave me the output as requested by you.

below is the script i used:

Data:

//Precedence Load
Load *,
txt&price;

//Inline load to load dummy data
Load * inline [
mat_num, txt, price
112,pant,20
113,pant,20
211,shirt,10
221,shirt,10
311,shirt,30
];

//deriving the count price and joining the data
join (Data)
Load
txt&price,
count(price) as c_prc
Resident Data
group by txt, price;

//deriving the match and found logic

Join (Data)
Load
txt&price,
if(c_prc > 1, 'Yes','No') as Match_Found
Resident Data;

Exit Script;

Re: Compare to values and show both Matchfound as Yes

Daniel1908 — Wed, 02 Jun 2021 06:44:44 GMT

Unfortunately the data load did not work.

Anyway I got the Matchfound worked with an expression in the worksheet:

=
AGGR(If(Count([Price])>1,If(Count(Text)>1,'Match','No Match'),'No Match'),[Price], Text)

Now I want to count the Material Numbers which got a Match.
An Idea how to add this?

Also the filter function does not work correctly in the table.
If I click on Match it selects the No Match ones as well.

Thanks for your help.

Kind Regards
Daniel

Re: Compare to values and show both Matchfound as Yes

Sammy_AK — Thu, 03 Jun 2021 11:21:11 GMT

try this:

sum(if (AGGR(If(Count([Price])>1,If(Count(Text)>1,'Match','No Match'),'No Match'),[Price], Text)= 'Match',1))

Re: Compare to values and show both Matchfound as Yes

Daniel1908 — Mon, 07 Jun 2021 07:42:35 GMT

The expression shows ok but I get the message "invalid dimension".

An Idea how to count the material numbers which got a match?

Kind Regards
Daniel