Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Convert Straight Table to an Aggregated Table

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

Capture.PNG

Any ideas?

Father/Husband/BI Developer
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Convert Straight Table to an Aggregated Table

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

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,Plant,StorLoc))


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

Father/Husband/BI Developer

View solution in original post

9 Replies
Highlighted
Specialist
Specialist

Re: Convert Straight Table to an Aggregated Table

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.

Highlighted
Creator III
Creator III

Re: Convert Straight Table to an Aggregated Table

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

Father/Husband/BI Developer
Highlighted

Re: Convert Straight Table to an Aggregated Table

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]))
Highlighted
Creator III
Creator III

Re: Convert Straight Table to an Aggregated Table

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.

 

  

 

Father/Husband/BI Developer
Highlighted

Re: Convert Straight Table to an Aggregated Table

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))
Highlighted
Creator III
Creator III

Re: Convert Straight Table to an Aggregated Table

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

Father/Husband/BI Developer
Highlighted
Creator III
Creator III

Re: Convert Straight Table to an Aggregated Table

No luck with that @sunny_talwar ?

Maybe,  @swuehl , @Gysbert_Wassenaar  @tresesco , @Kush , @alexandros17 , do you have any ideas?

Father/Husband/BI Developer
Highlighted
Digital Support
Digital Support

Re: Convert Straight Table to an Aggregated Table

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Creator III
Creator III

Re: Convert Straight Table to an Aggregated Table

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

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,Plant,StorLoc))


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

Father/Husband/BI Developer

View solution in original post