Calculate true or false

[johnan]

Hi best community!
I want to show if a calculate is ok in this way:
1. I have a frozen amount (576 pc)
2. We have a  3 times a calculate amount (600 and 588 and 600 again)

ArtKey	Location	Frozen	Time	Group Code	Count	Variance
225-202401	AAB-149-0C	576	14:15:39	AAB	600	24
225-202401	AAB-149-0C	576	14:16:38	SPOTCHECK	588	-12
225-202401	AAB-149-0C	576	14:17:14	SPOTCHECK	600	12

And it could go on until we got 2 same calculations and if so is okey!

So i want i a new object based on the calculated mark this with OK, this because we have count 600 2 times.
Like this:

Location	ArtKey (F)	NumberOfCount	Item	Variance	Result
AAB-149-0C	225-202401	3	225-202401	24	OK

Or this is ok to: (Fozen get a hit on count 576 = 576 at time 14:16:38) and it's ok to!

ArtKey	Location	Frozen	Time	Group Code	Count	Variance
225-202401	AAB-149-0C	576	14:15:39	AAB	600	24
225-202401	AAB-149-0C	576	14:16:38	SPOTCHECK	576	-12

Doable?

Br

John

[edwin]

from your description, there are a couple of things that need to be clarified so your problem could be understood.

it is not clear when the records will be flagged as OK.  the first scenario had a variance of 24 and it is ok, the second had a variance of 12 and it is OK because one of the rows had a match.

in your last example when count and frozen are both 576, why si the variance -12?  so what is the definition of variance?

  what are the business rules?  what are the conditions to flag it as OK and not OK?

[johnan]

Ah, sorry fot that!
This is from a inventory from a warehouse 🙂

The variance should be the same (24), like this:

ArtKey	Location	Frozen	Time	Group Code	Count	Variance
225-202401	AAB-149-0C	576	14:15:39	AAB	600	24
225-202401	AAB-149-0C	576	14:16:38	SPOTCHECK	588	12
225-202401	AAB-149-0C	576	14:17:14	SPOTCHECK	600	24

In this scenario we have count a location 3 times at diffrent timestamps.
The frozen is the amount the database is hold. And now we have point out that we have count 600, 2 times, and that's ok and result is okay. So i want a simple object that shows me like this:

Location	ArtKey (F)	NumberOfCount	Item	Variance	Result
AAB-149-0C	225-202401	3	225-202401	24	OK

The next example is, if you count the amount at anytime as the same as frozen, it's ok too.

Is not OK if we have diffrent calculations or just one calculation

Hopes this will clarified a bit??

[Saravanan_Desingh]

Are you looking something like this?

tab1:
LOAD * INLINE [
    ArtKey, Location, Frozen, Time, Group Code, Count, Variance
    225-202401, AAB-149-0C, 576, 14:15:39, AAB, 600, 24
    225-202401, AAB-149-0C, 576, 14:16:38, SPOTCHECK, 588, 12
    225-202401, AAB-149-0C, 576, 14:17:14, SPOTCHECK, 600, 24
    225-202402, AAB-149-0D, 576, 14:15:39, AAB, 600, 24
    225-202402, AAB-149-0D, 576, 14:16:38, SPOTCHECK, 576, 12
    225-202403, AAB-149-0E, 576, 14:17:14, SPOTCHECK, 600, 24
    225-202404, AAB-149-0F, 576, 14:15:39, AAB, 570, 24
    225-202404, AAB-149-0F, 576, 14:16:38, SPOTCHECK, 571, 12    
];

Left Join(tab1)
LOAD ArtKey, Location, Count(ArtKey) As C1, Concat(Frozen=Count) As T1
Resident tab1
Group By ArtKey, Location;

Left Join(tab1)
LOAD ArtKey, Location, Count, Count(Count) As C2
Resident tab1
Group By ArtKey, Location, Count;

Left Join(tab1)
LOAD ArtKey, Location, If(C1=1, 'Not OK', If((C1>1 And C2=2) Or (C1>1 And Index(T1,'-1')>0), 'OK', 'Not OK')) As tResult
Resident tab1;

tab2:
LOAD ArtKey, Location, If(Index(Concat(tResult='OK'),'-1')>0,'OK','Not OK') As Result
Resident tab1
Group By ArtKey, Location;

[Saravanan_Desingh · Accepted solution]

Output:

[johnan]

Hi Saran, i think so!

I will try this, i'll be back 🙂

[johnan]

Nearly 🙂

This is okay to:

ArtKey, Location, Frozen, Time, Group Code, Count, Variance
    225-202401, AAB-160-0D, 1024, 14:15:39, AAB, 1024, 0

[johnan]

I think a got it, i can easy hide the variance with zero.
And i think a figured out if you count sam location up to 6 times

Thanx 🙂