topic Re: Compare 2 Values in different rows in App Development

Compare 2 Values in different rows

Daniel1908 — Mon, 10 May 2021 08:52:35 GMT

Hello Together,

currently I compare 2 Values from different rows.
This works fine.

But now I want just to count the Material Number if the Text and the Price are equal to each other.

For example in the below table only count Text = T-Shirt with Price 10,00€.

I am using the below in the 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";

Re: Compare 2 Values in different rows

Dalton_Ruer — Mon, 10 May 2021 13:43:56 GMT

// Load the data
StockData:
Load * Inline [
Price, Text, MaterialNumber
10000, t-shirt, 93849
20000, pants, 93002
10000, t-shirt, 88493
30200, pants, 98390
20000, pants, 18837
23000, shirts, 93982
22000, skirts, 39820
22010, skirts, 3921983
];

// Go through data to find material numbers where the price and text are identical
Join
Load
Price, Text, MaterialNumber,
IF (Price = Previous(Price) and Text = Previous(Text), 'Yes', 'No') as MatchFound
resident StockData
order by Price, Text, MaterialNumber

Yields these results:

If it's a count you are looking for you can simply change Yes and No to 1 and 0. Or use Dual('Yes', 1), Dual('No', 0)

and then you could SUM the duplicatefound field for the count.

Re: Compare 2 Values in different rows

Daniel1908 — Mon, 17 May 2021 12:04:58 GMT

The Matchfound worked.

Is it possible to show both as 'Yes'?

I would like to filter and show only the ones which are equal to each other.

Kind Regards,
Daniel

Re: Compare 2 Values in different rows

Daniel1908 — Wed, 19 May 2021 08:20:03 GMT

Hello Together,

I tried to use asc and desc, unfortunately without success.

Order by Price, Text, MaterialNumber asc;

Order by Price, Text, MaterialNumber desc;

Is there any Idea how to show both Matchfound with "Yes"?

Kind Regards,
Daniel