topic Re: Left join in set analysis? in App Development

Left join in set analysis?

qlikeers — Thu, 28 Jul 2022 13:36:45 GMT

Hi!

I have a table as in the example.

Is it possible to get the expected table in set analysis?

The point is that I need to create a list of products with one exclusion (days) first, and then add the sum of value (for a date) to those products without the exclusion (as in the example). All from one table.

How can this be done?

Re: Left join w set analysis?

Andrei_Cusnir — Thu, 28 Jul 2022 12:50:20 GMT

Hello,

Perhaps the following example can help you:

1. I have loaded the dataset:

2. I have created a new Straight table chart with:

Dimension: Day
Dimension: ProdId
Dimension: =If(Aggr(NODISTINCT Sum(Days), ProdId)>=5, Aggr(NODISTINCT Sum(Value), ProdId), Null())

This expression will sum all the days based each ProdId if it is grater or equal to 5, it will sum the all the Value values per ProdId, otherwise it will return Null().

After that I just un-checked the option "Include null values" and the output is:

As you can see it is the same with the table that you have shared.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members.

Re: Left join w set analysis?

qlikeers — Thu, 28 Jul 2022 13:24:15 GMT

Thanks @Andrei_Cusnir ! This is a possible solution, I think.

But:

1) How do I make the total work in the table?
2) How do I get it to work at the bar chart?
3) Dimension: =If(Aggr(NODISTINCT Sum(Days), ProdId)>=5, Aggr(NODISTINCT Sum(Value), ProdId), Null()) - It should be a measure, shouldn't it?
4) Is there any way to do this without using aggr? With a large amount of data, it is a bit burdensome.

Re: Left join in set analysis?

Andrei_Cusnir — Thu, 28 Jul 2022 13:57:03 GMT

Hello,

Please find below the answers to your questions:

1) How do I make the total work in the table?

Ans: I am not sure what do you mean "total". Because, in the example provided above, we have "Sum(Sales)" which is the total of Sales. Can you please elaborate in detail as to what exactly you mean?

2) How do I get it to work at the bar chart?

Ans: You create a Bar chart with Day and ProdId as Dimension. Then you add the same expression =If(Aggr(NODISTINCT Sum(Days), ProdId)>=5, Aggr(NODISTINCT Sum(Value), ProdId), Null()) as Measure:

The outcome is the same:

As you can see it is 370 for cab and 300 for abc. bca is not visible as it has Null() (We ignored it due to total amount of days being less than 5)

3) Dimension: =If(Aggr(NODISTINCT Sum(Days), ProdId)>=5, Aggr(NODISTINCT Sum(Value), ProdId), Null()) - It should be a measure, shouldn't it?

Ans: In Table I have used it as Dimension to get the option "Include null values" and un-check it, to remove the rows entirely. In Bar chart, we have added it as Measure.

4) Is there any way to do this without using aggr? With a large amount of data, it is a bit burdensome.

Ans: Not 100% sure if it can be done. Because you have to calculate the total amount of days based on individual PordId, this is where Aggr() function is needed.

I hope that this information is helpful!

Re: Left join in set analysis?

qlikeers — Thu, 28 Jul 2022 14:28:55 GMT

Thanks for your reply!

Ad.1.
I mean the "Totals function" in measure.

Ad.2.
Ok, actually, and how to get to the higher level; without product dimension; only days?

Ad.3.
Sure i got it. My point is, then I can't use the "Total" function which is available in measures.

Ad.4.
Ok, thanks. I have to think about something more universal.

The question is how to universalise it more; assuming that there will be a table attached that will give the products more dimensions.

Re: Left join in set analysis?

Andrei_Cusnir — Mon, 08 Aug 2022 09:54:43 GMT

Hello @qlikeers,

Since the initial response looks like a potential solution to the description of the post, I believe that for the latest concerns that you have, it is better to create a new separate post. You can create a new post and elaborate further on the "how to universalize further" the solution that is posted here.

Re: Left join in set analysis?

qlikeers — Wed, 24 Aug 2022 12:45:54 GMT

@Andrei_Cusnir Thank you for all your help on the topic, it's invaluable!

However, may I have two more questions? I don't know, I don't think I fully understand this conditional clause, or rather the use of two aggr functions in it.

How to change it this way to get (without changing your assumptions):

1) Sum by days, without "ProdId",

2) Monthly average - as an arithmetic average over days in a month.

Re: Left join in set analysis?

Andrei_Cusnir — Thu, 25 Aug 2022 06:56:54 GMT

Hello @qlikeers,

If my understanding is correct, you would like to know why the following expression:
=If(
Aggr(NODISTINCT Sum(Days), ProdId)>=5,
Aggr(NODISTINCT Sum(Value), ProdId),
Null()
)

Has 2 aggregation functions inside. Please allow me to elaborate:

The initial requirement was: "Get the Sum(Value) for individual ProdId and return only the ProdIds whose Sum(Days) is grater than 5".

Therefore:

Aggr(NODISTINCT Sum(Days), ProdId)>=5 // This aggrigation will count all the days for individual products and will return the values: abc=15, bca=4 and cab=16
If the result is grater or equal than 5 it will execute Aggr(NODISTINCT Sum(Value), ProdId), otherwise, it will execute Null()
For the: Aggr(NODISTINCT Sum(Value), ProdId) // It is the same logic as above. It will sum all the values for individual ProdIds

NOTE: Please keep in mind that this solution might not be suitable for all use case scenarios.

Here is an answer to your questions:

1) Sum by days, without "ProdId"

ANS: I am not sure if this can be achieved. The reason is that you have to sum the days based on ProdId as condition. Otherwise you will either get the Sum of day for all the days in the dataset or you will get the sum of days for each row of the dataset individually. This means that we won't be able to distinguish, which days are related to which ProdId to ensure that we only show the ProdIds with Sum(Days) >= 5.

Example:

As you can see:

The Sum(Days) will give us the sum of the days per row, therefore we can't check which ProdId has all of its days grater than 5 to display it on out final output
The Sum(TOTAL Days) will give us 35, which means that based on this number we would display all the ProdIds in the final output, which is not what we need, since ProdId bca=4 and thus we have to exclude it from the final table
The solution that uses ProdId gives us the right number for each row, which means that checking that number we can return the sum of values for the particular product, if the result is grater or equal to 5.

2) Monthly average - as an arithmetic average over days in a month.
ANS: I am not 100% sure how to achieve it at the moment, but it looks like it is a different requirement from the initial one posted here. I would recommend posting it as a new community post and elaborating in detail as to what you have already implemented and demonstrate with screenshots what you have and what else do you expect to see, just like you did in this post's description. Then perhaps someone can share a valuable suggestion or in case I am able to assist I will also share my thoughts.

I hope that this additional information is helpful. In case the details shared regarding the questions that are related to the initial request are helpful, please mark it as solution to give further visibility to other users as well.

Re: Left join in set analysis?

qlikeers — Thu, 25 Aug 2022 08:02:19 GMT

Therefore:

Aggr(NODISTINCT Sum(Days), ProdId)>=5 // This aggrigation will count all the days for individual products and will return the values: abc=15, bca=4 and cab=16

@Andrei_Cusnir Thanks again for your help and your time!

Initially, I liked your concept very much and it seemed to work. One note as you write:

This aggrigation will count all the days for individual products and will return the values: abc = 15, bca = 4 and cab = 16

It shouldn't work that way. Should check each row in turn, without aggregating. So for the product abc it should take two lines, because each one meets the condition Days> = 5. For bca product it shouldn't take any line. And for the cba product, only the one where the value of Days> = 5.
Sum (Value) (after days) must be added to the list of the above ProdId (after days).
For some reason I have to do this outside of the "load script".
That is why I am writing about lef join in this topic, because in SQL it would look exactly like this (assuming the source table is tmp) :

select distinct tmp.Day, tmp.ProdId, tmp2.Val from tmp LEFT JOIN (select Day, ProdId, sum(Value) as Val from tmp group by Day, ProdId) as tmp2 ON tmp.Day = tmp2.Day and tmp.ProdId = tmp2.ProdId where tmp.Days >= 5

On the basis of such aggregated values, I could probably achieve anything I need.

Re: Left join in set analysis?

Andrei_Cusnir — Thu, 25 Aug 2022 10:31:14 GMT

Hello,

I see that you would like to achieve this use case scenario within script by using SQL statement. The solution provided in this post will give you the option to achieve the output table in the front end part so it is partially a solution as you have mentioned and the steps might still help someone else from the community to achieve similar outcome in front end.

However for your particular use case scenario where you have the requirement to make it work in script and with SQL statement only, I might not be able to help you as I am not very familiar with this. Since the post has also many comments, it makes it harder for other community members that might know this topic better, to find it and provide you a solution, therefore I recommend you to post a new topic with the initial screenshots attached and ensure that you specify that the requirement it is to do it with script and SQL only. Otherwise, other community members might still share a similar front end solution with Aggr().

I hope that this information is helpful.

Re: Left join in set analysis?

qlikeers — Thu, 25 Aug 2022 10:54:49 GMT

I wrote the SQL only to show what I would like to achieve the end result. I would like to do this on the front-end. I think your idea is good, but I have no idea how to adapt it.

Re: Left join in set analysis?

qlikeers — Mon, 05 Sep 2022 13:25:38 GMT

I guess I found a solution to the problem and it was easier than expected 🙂

Dimensions:
- Days
- ProdId

Measures:
sum(
{
<
[ProdId]=p({<[Days] = {">=5"}>} [ProdId])
>
} [Value])

Useful stuff [CLICK]

Thanks again for your help!