Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
Any ideas?
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 ;
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
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 ;
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
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.
Please find attached a part of the qvw. Hope that helps
Max(projectie) will show 35329.
Thanks for helping, I hope we find a sollution.
One comment for now:
You need to define the MAPPING LOAD tables before calling them in an applymap() in your script.
I re-arranged some sheets, before uploading. sorry
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!