Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have the following problem:
I have a table containing article-data (sample-data):
ARTICLE_ID | PRODUCTGROUP_ID | STARTDATE | ADDED_AT_DATE | VALUE |
---|---|---|---|---|
1 | 1 | 1 | 1 | 100 |
1 | 1 | 5 | 1 | 200 |
2 | 2 | 1 | 1 | 300 |
3 | 2 | 1 | 3 | 400 |
Now what I need to do is to add a field to every row of this table.
Those fields are calculated by summing up all values of the table which have the same productgroup-id as in the current row and also need to have added_at_date in between the startdate and the startdate + 7.
Is there an efficient way to solve this problem?
I tried to create a new table called productgroup, which contains data like the productgroup_id, added_ad_date and dailyvalue (dailyvalue is an accumulation of all values for every productgroup_id and added_ad_date).
Doing so I am clueless about how to accumulate the data out of this table for every article_id in the article-table, since it would need to be accumulated for every row over a dynamic amount of rows in the productgroup-table.
I hope you can help me with ideas or techniques to solve this problem
I need to add a calculated field for every article_id.
Its value would be:
sum(value)
group by (productgroup_id)
where (productgroup_id = current_article_id)
and (added_at_date >= current_startdate)
and (added_at_date <= current_startdate + 7)
Is there no way to do it in Qlik Sense?
I can do the calculation in excel, using a simple sumifs-function.
Apparently this problem is too difficult to be solved with qlik-sense algorithms.
Therefor I had to reduce the problem and therefor the functionality.
Using a single date-value to calculate the productgroup value instead of a variable amount is possible.
I'm not sure that it couldn't be solved with qlik sense - but I don't really understand how your data look like and what do you want to do. What should be the expected output?
- Marcus
The output for this sample data would look like this:
ARTICLE_ID | PRODUCTGROUP_ID | STARTDATE | ADDED_AT_DATE | VALUE | RESULT |
1 | 1 | 1 | 1 | 100 | 300 |
1 | 1 | 5 | 1 | 200 | 0 |
2 | 2 | 1 | 1 | 300 | 700 |
3 | 2 | 1 | 3 | 400 | 700 |
it is a group by productgroup_id, where for every article_id the value is summed up, if the added_at_date is in between the startdate and the startdate + 7.
As I mentioned this can be done if the startdate is fixed, but I don't see a way to do this with a variable startdate (depending on the article_id)
I think that you rather didn't need an aggregation load and more a check and summing of previous loaded records which could be done within a sorted load with: Peek() or Previous() ?
- Marcus
Need to understand this better
Can you denote how each of the RESULT's were calculated.
ARTICLE_ID | PRODUCTGROUP_ID | STARTDATE | ADDED_AT_DATE | VALUE | RESULT |
1 | 1 | 1 | 1 | 100 | 300 How 300? |
1 | 1 | 5 | 1 | 200 | 0 why Zero? |
2 | 2 | 1 | 1 | 300 | 700 How 700? |
3 | 2 | 1 | 3 | 400 | 700 how 700? |
Hi Dennis
Temp:
LOAD ARTICLE_ID,
PRODUCTGROUP_ID,
STARTDATE,
ADDED_AT_DATE,
VALUE
FROM
"https://community.qlik.com/message/1117037"
(html, codepage is 1252, embedded labels, table is @1);
Left Join LOAD PRODUCTGROUP_ID,Sum(VALUE) as VALUE1
Resident Temp
Group By PRODUCTGROUP_ID;
LOAD *,
If(ADDED_AT_DATE >= STARTDATE and ADDED_AT_DATE <= STARTDATE +7,VALUE1,0) as RESULT
Resident Temp;
DROP Field VALUE1;
Drop Table Temp;
Regards,
Antonio
Hi Antonio,
I took a look into your solution and for the sample data it works very well.
Unfortunately the main problem does not seem to be represented by the given sample data.
This problem lies within the part, where you calculate the product-group values:
Left Join LOAD PRODUCTGROUP_ID,Sum(VALUE) as VALUE1
Resident Temp
Group By PRODUCTGROUP_ID;
It does not yet have the condition to only sum up data inside the given timespan.
I have tested it by adding two lines of sample data:
ARTICLE_ID | PRODUCTGROUP_ID | STARTDATE | ADDED_AT_DATE | VALUE | RESULT |
---|---|---|---|---|---|
1 | 1 | 1 | 1 | 100 | 525 |
1 | 1 | 5 | 1 | 200 | 275 |
1 | 1 | 4 | 4 | 225 | 500 |
1 | 1 | 5 | 10 | 275 | 275 |
2 | 2 | 1 | 1 | 300 | 700 |
3 | 2 | 1 | 3 | 400 | 700 |
The value is, as I tried to explain, calculated for each and every article, not for the complete product group.
Hope this helps understanding the problem better, for the other ones helping as well.
-Dennis