Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joris_lansdaal
Creator
Creator

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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 ;

View solution in original post

9 Replies
swuehl
MVP
MVP

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

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
Creator
Creator
Author

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

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
Creator
Creator
Author

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

joris_lansdaal
Creator
Creator
Author

Max(projectie) will show 35329.

Thanks for helping, I hope we find a sollution.

swuehl
MVP
MVP

One comment for now:

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

joris_lansdaal
Creator
Creator
Author

I re-arranged some sheets, before uploading. sorry

joris_lansdaal
Creator
Creator
Author

‌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!