topic Re: Calculate a Field in script based on other calculated Fields in QlikView

Calculate a Field in script based on other calculated Fields

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

Hi - I need to Calculate a Field based on previous fields I calculated in the script

I basically need to calculate [Net Available Stock] based on [Stock IN], [Stock] and [Demand requested ] Calculated previously in the script.

Please see code sample bellow. if someone could help would be great. Many thanks

NoConcatenate
Metrics:
Load
SuperKeyM,
DATA,
QTY,
DateKey as Week,
[Weekly Avarage],
Period
Resident MasterData;

//// Calculate supply In ////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Supply In]
Resident Metrics
Where DATA='SI'
Group by SuperKeyM;

////Calculate Stock ////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Stock]
Resident Metrics
Where DATA='ST'
Group by SuperKeyM ;

//// Calculate Demand past////
Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Demand Requested1]

Resident Metrics
Where Period<>'Future' and DATA='TD'
Group by SuperKeyM;

//// Calculate Demand Future////

Left Join(Metrics)
Load
SuperKeyM,
sum(QTY) as [Demand Requestedcalc2]

Resident Metrics
Where Period='Future' and DATA='TD'
Group by SuperKeyM;

/// Calculate Compare Demand With Avarage////

Left Join(Metrics)
Load
SuperKeyM,
If([Demand Requestedcalc2]>[Weekly Avarage],[Demand Requestedcalc2],[Weekly Avarage]) as [Demand Requested2]
Resident Metrics
Where Period='Future';

///////////////////////////////////////Doesn't work from here //////////////////////////////////////////////////////////

/// Calculate Total Demand requested]

Left Join(Metrics)
Load
SuperKeyM,
[Demand Requested1] + [Demand Requested2] as [Demand requested]
Resident Metrics;

/////Calculate Net Available Stock /////

NoConcatenate
MetricsFinal:
Load
SuperKeyM,
[Supply In],
[Stock],
Week,
[Demand requested],
If(Week='W-13',(([Stock]+ [Supply In]) - ([Demand requested])), (((Peek([Net Available Stock])) +[Supply In])- ([Demand requested]))) as [Net Available Stock]
Resident Metrics;
Drop Table Metrics;

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Fri, 31 May 2019 11:52:42 GMT

What issue are you running into when you run your script? Does it errors out?

Re: Calculate a Field in script based on other calculated Fields

Pestaninha — Fri, 31 May 2019 12:08:28 GMT

Hi Sunny - it does load the fields [Demand requested] and [Net Available Stock] but doesn't return any value

Thanks

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Fri, 31 May 2019 12:31:16 GMT

Would you be able to provide sample data to see what might be the reason....

Re: Calculate a Field in script based on other calculated Fields

Pestaninha — Fri, 31 May 2019 12:52:22 GMT

Sample attached

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Fri, 31 May 2019 13:00:26 GMT

Is this the output you are getting? or is this sample data? It seems that Net Available Stock is already a field which is why it might be the output I feel... also where is Week field? Is DateKey = Week?

Re: Calculate a Field in script based on other calculated Fields

Pestaninha — Fri, 31 May 2019 13:09:47 GMT

Hi Sunny, the first Table is a sample of the data, the second table is what i am getting with my script, also what i want to achieve but with correct values. Demand requested is returning all 0. If I Load fields Demand requested1 and Demand requested2 they return the correct values, but Demand Requested1 + Demand Requested1 = Demand requested does not work

Yes, DateKey = Week

Many Thanks

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Fri, 31 May 2019 13:31:24 GMT

sorry, I missed the tabs on the attached Excel.... checking it now

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Fri, 31 May 2019 13:54:12 GMT

Not 100% sure on what output you are looking to get, but does this look like what you want?

Script used

Metrics: LOAD SuperKeyM, DateKey as Week, Period, DATA, QTY, [Weekly Avarage] FROM [..\..\Downloads\Data Sample.xlsx] (ooxml, embedded labels, table is Data); //// Calculate supply In //// Left Join(Metrics) LOAD SuperKeyM, Sum(QTY) as [Supply In] Resident Metrics Where DATA = 'SI' Group by SuperKeyM; ////Calculate Stock //// Left Join(Metrics) LOAD SuperKeyM, Sum(QTY) as [Stock] Resident Metrics Where DATA = 'ST' Group by SuperKeyM; //// Calculate Demand past//// Left Join(Metrics) LOAD SuperKeyM, Sum(QTY) as [Demand Requested1] Resident Metrics Where Period <> 'Future' and DATA = 'TD' Group by SuperKeyM; //// Calculate Demand Future//// Left Join(Metrics) LOAD SuperKeyM, Sum(QTY) as [Demand Requestedcalc2] Resident Metrics Where Period = 'Future' and DATA = 'TD' Group by SuperKeyM; ///Calculate Compare Demand With Avarage, Calculate Total Demand requested, & Calculate Net Available Stock ///// tmpMetrics: LOAD SuperKeyM, [Supply In], [Stock], Week, RangeSum([Demand Requested1], If([Demand Requestedcalc2]>[Weekly Avarage],[Demand Requestedcalc2],[Weekly Avarage])) as [Demand requested] Resident Metrics; MetricsFinal: LOAD *, If(Week = 'W-13', RangeSum([Stock], [Supply In], -[Demand requested]), RangeSum(Peek('Net Available Stock'), [Supply In], -[Demand requested])) as [Net Available Stock] Resident tmpMetrics Order By SuperKeyM, Week; DROP Tables Metrics, tmpMetrics;

Re: Calculate a Field in script based on other calculated Fields

Pestaninha — Fri, 31 May 2019 14:29:35 GMT

Very much Sunny, thanks a million 🙂

Re: Calculate a Field in script based on other calculated Fields

Pestaninha — Tue, 04 Jun 2019 13:18:13 GMT

Hi Sunny - I still have an issue with this, the table is duplicating values, I need a value by SuperKeyM otherwise calculations are incorrect. Any ides how I do this?

Many Thanks

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Tue, 04 Jun 2019 16:45:43 GMT

Would you be able to share an example?

Re: Calculate a Field in script based on other calculated Fields

qliktowin — Thu, 20 Feb 2020 16:21:35 GMT

Hi Sunny,

I have a similar case want to know which one would be best solution. Calculating the field again by doing a resident load or calculating it while creating a chart.

Re: Calculate a Field in script based on other calculated Fields

sunny_talwar — Thu, 20 Feb 2020 16:42:03 GMT

It depends....in most cases, I would say that it would be better to do it in the script... but this is not always 100% true. If your data model gets really complicated and huge, it might be better to do it on the front end.