topic Re: Calculate true or false in QlikView

Calculate true or false

johnan — Wed, 10 Feb 2021 13:56:33 GMT

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?

John

Re: Calculate true or false

edwin — Wed, 10 Feb 2021 15:40:32 GMT

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?

Re: Calculate true or false

johnan — Wed, 10 Feb 2021 16:11:46 GMT

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??

Re: Calculate true or false

Saravanan_Desingh — Thu, 11 Feb 2021 04:47:22 GMT

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;

Re: Calculate true or false

Saravanan_Desingh — Thu, 11 Feb 2021 04:47:52 GMT

Output:

Re: Calculate true or false

johnan — Thu, 11 Feb 2021 05:33:46 GMT

Hi Saran, i think so!

I will try this, i'll be back 🙂

Re: Calculate true or false

johnan — Thu, 11 Feb 2021 05:56:58 GMT

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

Re: Calculate true or false

johnan — Thu, 11 Feb 2021 09:15:44 GMT

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 🙂