How to fill a column with values based on other columns?

lalacloudvisuals — Sat, 16 Nov 2024 01:56:22 GMT

Hi,

I have a table where I'd like to create a set of derived columns based on columns but I'm having a lot of difficulty with coding the logic. Any help would be highly appreciated!

Suppose my current table is such that:

Current table:

Product Type	Fruit	Color	Color Known?	Individual Color	Count
Individual	Apple		N		4
Individual	Grapes		Y	Red	6
Basket	Apple	Red			1
Basket	Apple	Yellow			3
Basket	Apple	Green			2
Basket	Grapes	Green			2
Basket	Grapes	Red			4
Basket	Orange	Orange			3

Essentially, suppose fruit is being sold two ways: individually vs. combined in a basket. We want to be able to calculate the number of fruit associated with each color, with drill-down into type. If fruit is being sold individually, we either know or do not know the color. If known, we can designate the color in the "Individual Color" column. If unknown (i.e. Color Known? = N), then we can assume the color distribution based on the distribution of same fruit type in a basket and then allocate to each color.

For example, we do not know the color of individual apples but know that a basket of apples being sold contains 1 Red, 3 Yellow, and 2 Green (i.e. total 6 apples). In other words, in the basket, 17% is Red (1/6), 50% is Yellow (3/6), and 33% (2/6) is Green. Applied to the individual apples (total 4), we can assume there are 0.7 Red apples (4 * 0.17), 2 Yellow apples (4 * 0.5), and 1.3 Green apples (4 * 0.33). Assume decimals are allowed.

As a result our final table should look like the below:

Final data table

Product Type	Fruit	Color	Final Color	Count	Final Count
Individual	Apple		Red		0.7
Individual	Apple		Yellow		2
Individual	Apple		Green		1.3
Individual	Grapes		Green		6
Basket	Apple	Red	Red	1	1
Basket	Apple	Yellow	Yellow	3	3
Basket	Apple	Green	Green	2	2
Basket	Grapes	Green	Green	2	2
Basket	Grapes	Red	Red	4	4
Basket	Orange	Orange	Orange	3	3

In doing so, I can then build a chart with dimensions Final Color and Fruit to get the total # of fruit for each color.

I'm trying to implement these during the Load script. I am trying to get the Final Color logic such that:

if Product Type = Basket then Final Color = Color
if Product Type = Individual then Final Color = same as Color when Product Type = Basket

Likewise, Final Count column logic should be:

if Product Type = Basket then Final Count = Count
if Product Type = Individual then Final Count = Individual Color Count * Distribution of Color under Basket

For Final Count, I tried creating an intermediary column in the load such that:

Load * , sum(Total <[Product Type], [Fruit], [Color]> [Count]) as [Calculation1]

However I ran into an error saying that Sum only allows 1 parameter even though the sum formula works fine as an expression when creating a table.

Thanks in advance!

Re: How to fill a column with values based on other columns?

rubenmarin — Fri, 15 Nov 2019 08:10:00 GMT

Hi, are you sure the error is because of that row of code? it only shows one parameter for sum.

You can try with rangesum if you need to add more than one field.

Or upload a sample so we can take a look.

Re: How to fill a column with values based on other columns?

Brett_Bleess — Tue, 19 Nov 2019 16:47:56 GMT

Here is the Help link for Sum as used in the Script, which is potentially different than what is allowed in an Expression, so be sure to compare things:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/AggregationFunctions/sum.htm

I agree with Ruben otherwise, you likely will have to attach a sample app etc. in order for folks to be able to help further, as this is one of those where you likely need to see a lot more in order to try to figure out what is going wrong.

Regards,
Brett

topic Re: How to fill a column with values based on other columns? in QlikView

How to fill a column with values based on other columns?

Re: How to fill a column with values based on other columns?

Re: How to fill a column with values based on other columns?