topic Re: Convert Straight Table to an Aggregated Table in QlikView

Convert Straight Table to an Aggregated Table

dapostolopoylos — Sat, 16 Nov 2024 00:27:31 GMT

Hello, everyone.

My problem is this:

I have created a really large straight table with many dimensions (many of them calculated) and many expressions.

This table works more like an Excel table rather than a QlikView straight table.

The expressions rely heavily on the other columns through references and there is a lot of business logic in them (if this then that or...).

Nevertheless the numbers are ok and business aproves them and now they want me to create the same table on company level by year and month... and it seems that i cannot make the numbers be correct in the new table...

The expression that bugs me most is this

=If(Category='Consumption' and [Actual Quantity]=0,
0,
if(Category='Production',
Null(),
if(ProducedMaterialGroupCode='90014' or NoCostFlag=1 or ProducedMaterialCode='2280249',
0,
[Theoretical Requirement]+[Actual Quantity]
)
)
)

where [Theoretical Requirement]and[Actual Quantity]are other columns of the table.

If it helps what i want to do is shown below

Any ideas?

Re: Convert Straight Table to an Aggregated Table

vamsee — Thu, 18 Jun 2020 16:03:59 GMT

May be try

=Sum(Aggr(Sum({<Category-={'Consumption', 'Production'},[Actual Quantity]-={0},ProducedMaterialGroupCode={'90014'}, NoCostFlag-={1} , ProducedMaterialCode-={'2280249'}>}[Theoretical Requirement]+[Actual Quantity]), Company_Code))

If this doesn't work, I would share your qvw for people to look at your calculated dimensions.

Re: Convert Straight Table to an Aggregated Table

dapostolopoylos — Thu, 18 Jun 2020 16:39:44 GMT

Thank you for your reply but it doesn't work...

Theroretical Requirement is itself calculated and i cannot exclude both 'Consumption' and 'Production' from Category, these are all the values of the field...

Nevertheless, thank you, i wlli play around a little with your central idea of Aggr

Re: Convert Straight Table to an Aggregated Table

sunny_talwar — Thu, 18 Jun 2020 16:43:29 GMT

You can also do this

Sum(If(Category = 'Consumption' and [Actual Quantity] = 0, 0, If(Category = 'Production', Null(), If(ProducedMaterialGroupCode = '90014' or NoCostFlag = 1 or ProducedMaterialCode = '2280249', 0, RangeSum([Theoretical Requirement], [Actual Quantity])))))

or just create a flag in the script if possible (i.e. if all the fields are located in a single table)

LOAD *, If(Category = 'Consumption' and [Actual Quantity] = 0, 0, If(Category = 'Production', Null(), If(ProducedMaterialGroupCode = '90014' or NoCostFlag = 1 or ProducedMaterialCode = '2280249', 0, 1) as CalcFlag

and then this

Sum({<CalcFlag = {1}>} RangeSum([Theoretical Requirement], [Actual Quantity]))

Re: Convert Straight Table to an Aggregated Table

dapostolopoylos — Fri, 19 Jun 2020 07:39:26 GMT

Hello, @sunny_talwar

I tried the first sol;utiom you provided but unforunately it didn't work...

I remind you that [Theroretical Requirement] and [Actual Quantity] are expression columns that i don't want them to be show in my aggregated new chart.

To make more clear what i am talking about, i'm attaching a sample qvw that contains the actual table i already created and the aggregated one that i want to get right...

I hope it will help.

Re: Convert Straight Table to an Aggregated Table

sunny_talwar — Fri, 19 Jun 2020 14:14:42 GMT

May be this for Jan

Sum({<PostingMonth = {1}>}Aggr( If(Category='Consumption' and Sum(Quantity)=0, 0, if(Category='Production', Null(), if(ProducedMaterialGroupCode='90014' or NoCostFlag=1 or ProducedMaterialCode='2280249', 0, (If(Category='Consumption' and Sum(Quantity)=0, 0, if(Category='Consumption' and MaterialCodeGroup='21', Qty101_102, (([BOM Requirement]/BaseQuantity)*Qty101_102) ) ))+Sum(Quantity) ) ) ) ,PostingMonth,ProductionOrder,ComponentCode, ProducedMaterial, ProductionBatch, Component, Category))

Re: Convert Straight Table to an Aggregated Table

dapostolopoylos — Fri, 19 Jun 2020 15:25:36 GMT

Thank you so much for your effort @sunny_talwar !

There are 8 material categories in my data model and your expression works perfectly for the 6 of them.

Is there a chance that you might take a look on those two left behind also?

I am attaching another sample file with data from those 2 categories (Packaging Materials, Additional Materials)

Thank you again! 🙂

Re: Convert Straight Table to an Aggregated Table

dapostolopoylos — Tue, 23 Jun 2020 06:20:41 GMT

No luck with that @sunny_talwar ?

Maybe, @swuehl , @Gysbert_Wassenaar @tresB , @Kushal_Chawda , @alexandros17 , do you have any ideas?

Re: Convert Straight Table to an Aggregated Table

Brett_Bleess — Wed, 08 Jul 2020 12:28:12 GMT

You may find the following Design Blog useful in relation to the use of AGGR in Set Analysis:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

Re: Convert Straight Table to an Aggregated Table

dapostolopoylos — Wed, 08 Jul 2020 15:20:42 GMT

Thank you for response, it's funny but today i finally figured it out!

@sunny_talwar guided me to the right path and the only addition to his solution, according to the sample(3).qvw file is the following

Just added the last two dimension (Plant, StorLoc) in the Aggr function and everything worked!