Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

joris_lansdaal
Not applicable

Help - last value in a serie of lines

Hi all,

I'm stuck with the following issue. In my datamodel I Have three components that make a stock projection.

- Demand_Aantal (demand in units)

- Voorraad (inventory units))

- Lopend_Aantal_B (Ordered units)

In the end I need the fact Projectie with only one projection per %BQS_Datum_Key and the other lines get a 0.

I have the sorting and sum all worked out in Projectie2 (with a rangesum). And with mapping, via below script, i will get what I want. However....I don't know how to get the last line like in the example.

Projectie:

Mapping

Load

BQS_ind&Datum as %BQS_Datum_Key,

Min(Projectie2) as Projectie

resident Temp_Projectie_1

Group by BQS_ind&Datum ;

Demand:

Load *,

if(%BQS_Datum_Key=Peek(%BQS_Datum_Key),0,ApplyMap('Projectie',%BQS_Datum_Key,0))as Projectie

resident Temp_Projectie_1;

Store Demand into $(vStorePath)\Datamodel_Projection.CSV (txt);

Projectie.PNG

Any ideas?

Tags (2)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Help - last value in a serie of lines

Or are you asking for another mapping table?

Projectie_Max:

Mapping

Load

BQS_ind&Datum as %BQS_Datum_Key,

Max(Projectie2) as Projectie_LastLine

resident Temp_Projectie_1

Group by BQS_ind&Datum ;

9 Replies
swuehl
Not applicable

Re: Help - last value in a serie of lines

I don't really understand your request.

The cell you are pointing to in your screen shot should already be calculated before, right ("I have the sorting and sum all worked out in Projectie2 (with a rangesum).")?

What does your current script deliver compared to that screenshot?

Maybe I am just a bit confused about what you are showing being the expected or the actual output?

Maybe just show both, best in combination with a small working sample QVW.

Regards,

Stefan

swuehl
Not applicable

Re: Help - last value in a serie of lines

Or are you asking for another mapping table?

Projectie_Max:

Mapping

Load

BQS_ind&Datum as %BQS_Datum_Key,

Max(Projectie2) as Projectie_LastLine

resident Temp_Projectie_1

Group by BQS_ind&Datum ;

joris_lansdaal
Not applicable

Re: Help - last value in a serie of lines

I need the value 35119,91. When I use Min(Projectie2) as ProjectieI I get 1169, if I use Max(Projectie2) as Projectie I will get 35329.

I look at FirstSortedValue, but don't know how or if it will work.

Yes to your question below

regards

swuehl
Not applicable

Re: Help - last value in a serie of lines

What is your table actually showing?

If you are showing Projectie2 in the table, Max(Projectie2) grouped by your %BQS_Datum_Key should be 35119,91.

joris_lansdaal
Not applicable

Re: Help - last value in a serie of lines

Please find attached a part of the qvw. Hope that helps

joris_lansdaal
Not applicable

Re: Help - last value in a serie of lines

Max(projectie) will show 35329.

Thanks for helping, I hope we find a sollution.

swuehl
Not applicable

Re: Help - last value in a serie of lines

One comment for now:

You need to define the MAPPING LOAD tables before calling them in an applymap() in your script.

joris_lansdaal
Not applicable

Re: Help - last value in a serie of lines

I re-arranged some sheets, before uploading. sorry

joris_lansdaal
Not applicable

Re: Help - last value in a serie of lines

‌I found the flaw in the logic. I rearranged demand trough a different sort and created a rangesum within one date. After that i can always take the max result trough mapping.

thanks!