topic Stock information based on latest EMPTY message. in QlikView

Stock information based on latest EMPTY message.

dbosboom — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

First of all thanks in advance for helping! This community has been a huge help for me so far without even needing to post something. The time has finally arrived that I have to post something as I have not been able to find the right answer. The following has been keeping me busy for the last 2 days.

A customer wants to see the day-to-day stock information of their products per silo. Meaning that they select a date and they want to see everything that has been added to or used from that silo, previous to that date.

Their system creates a row in the database each time the silo is completely empty, meaning that I only have to calculate the stock from the selected date back in time until the first “Empty” message. Everything before this message is irrelevant.

I’ve tried coming up with a decent illustration of what I mean. See below:

Date	Time	Weight(kg)	Silo	Code
25/02/2016	04:00	+2000	6	008
25/02/2016	03:45	+1000	6	007
24/02/2016	23:01	+1000	6	006
24/02/2016	14:51	0	6	EMPTY
24/02/2016	14:50	-6700	6	005
24/02/2016	14:42	+2300	6	004
24/02/2016	14:32	+3400	6	003
24/02/2016	13:58	-1400	6	002
24/02/2016	13:42	+2400	6	001

Let´s say they have selected the 25th of February and silo 6. They now need only need to see the rows with code 006, 007 and 008.

My set analysis looks like this: SUM({<Date = {"<=$(=Max(Date))"}>}Weight)

But this also shows all the rows before the empty message.

I’ve also tried: =SUM({<Datum = {">=$(=Min({<Code = {'EMPTY'}>} Date)) <=$(=Max(Date))"}>}Weight) to get the minimum date with the code EMPTY but there can be more than 1 when going back in time.

Do you guys have any idea how to find the first EMPTY value prior to the selected date?

Thanks!

Re: Stock information based on latest EMPTY message.

sunny_talwar — Wed, 30 Nov 2016 08:31:45 GMT

Have you tried with Max instead of Min here?

=Sum({<Date = {">=$(=Max({<Code = {'EMPTY'}>} Date)) <=$(=Max(Date))"}>}Weight)

Re: Stock information based on latest EMPTY message.

dbosboom — Wed, 30 Nov 2016 08:37:54 GMT

I just did and that doesn't work either. Also when I only select the silo and nothing else (when using that expression), it shows the correct value in the table but when selecting a date, it shows all values. Multiple EMPTY messages.

Re: Stock information based on latest EMPTY message.

sunny_talwar — Wed, 30 Nov 2016 08:48:22 GMT

I am not sure I completely follow, would you be able to provide a sample where you are seeing this issue? It is difficult to understand multiple Empty when I am only seeing a single EMPTY in your sample.

Re: Stock information based on latest EMPTY message.

rubenmarin — Wed, 30 Nov 2016 09:08:26 GMT

Hi Dennis, maybe you can change the code dimension to a calculated dimension like:

=Aggr(Only({<DateTime={"<=$(Max({<Code={'EMPTY'}, DateTime={""<=$(=Max(DateTime))""}, Date>} DateTime))"}>} Code), Code)

Or use this condition in expression to filter values to show.

Re: Stock information based on latest EMPTY message.

tresB — Wed, 30 Nov 2016 09:09:13 GMT

Try expression like:

Only({<Date={'>$(=Date(Max({<Date,Code={EMPTY}>}Date))) <=$(=Date(Max(Date)))'}>}Code)

for weight,

Sum({<Date={'>$(=Date(Max({<Date,Code={EMPTY}>}Date))) <=$(=Date(Max(Date)))'}>}Weight)

Re: Stock information based on latest EMPTY message.

dbosboom — Wed, 30 Nov 2016 09:22:40 GMT

I've created a sample app with the data as is and translated the field names to English. In the field Code there's a value called Leegmelden, that is the EMPTY message that I'm talking about

Re: Stock information based on latest EMPTY message.

tresB — Wed, 30 Nov 2016 09:24:11 GMT

To be more accurate and include the timestamp as well in the analysis, you have to generate a timestamp field in the script, like:

LOAD Date,

    Time,

    [Weight(kg)],

    Silo,

    Code,

    Timestamp(Date+Time) as TimeStamp

FROM

[https://community.qlik.com/thread/241523]

(html, codepage is 1252, embedded labels, table is @1);

And then try expression like:

Sum({<Date,TimeStamp={'>$(=Timestamp(Max({<Date,Code={EMPTY}>}TimeStamp))) <=$(=Timestamp(Max(TimeStamp)))'}>}[Weight(kg)])

Assuming the selection would be in Date field.

Re: Stock information based on latest EMPTY message.

sunny_talwar — Wed, 30 Nov 2016 09:30:38 GMT

I guess tresesco‌ expression might make sense then:

=Sum({<Date = {">=$(=Date(Max({<Date, Code = {'Leegmelden'}>} Date))) <=$(=Max(Date))"}>}Weight)

Re: Stock information based on latest EMPTY message.

dbosboom — Wed, 30 Nov 2016 09:39:02 GMT

This works like a charm for the first Empty message and selecting a date after, see FirstEmpty.jpg. But when I select the date before 27-10-20, it shows everything again, see SecondEmpty.jpg. Using the Sample(3).qvw that Sunny T provided.

We're getting there though!

Re: Stock information based on latest EMPTY message.

sunny_talwar — Wed, 30 Nov 2016 09:42:57 GMT

Try this:

=Sum({<Date = {">=$(=Date(Max({<Date = {'<=$(=Max(Date))'}, Code = {'Leegmelden'}>} Date))) <=$(=Max(Date))"}>}Weight)

Re: Stock information based on latest EMPTY message.

dbosboom — Wed, 30 Nov 2016 09:49:22 GMT

So far this works! Thanks tresesco and Sunny T! (Can't tag you somehow)