Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a problem and i want to know your suggest for me.
I have a header table containing 3 differente range of date. (Plan)
A details table containing the definition of the header table (list of productid contained into Plan)
A fact table (Sales) containg the sales for each date and each product.
What i need?
Header:
LOAD * INLINE [
ID1, Sdate, FDate, SDatePP, FDatePP, SDatePY, FDatePY
1, 41263, 41271, 41249, 41257, 40892, 40900
2, 41248, 41256, 41234, 41242, 40877, 40885
];
Details:
Load * INLINE [
ID1, ID2
1,1
1,2
1,3
2,4
2,5
2,6
];
Sales:
LOAD * INLINE [
ID1, ID2, Date, Value
1 1 41264 100
1 2 41264 50
1 1 41250 99
1 2 41251 49
1 1 40893 98
1 2 40894 97
2 4 41249 10
2 5 41250 20
2 4 41235 9
2 5 41236 19
2 4 40878 8
2 5 40879 18
];
I want to select an id of the plan and to see the sum(value) for each period, a table like this
Id1 | Id 2 | ValueRange1 | ValueRange2 | ValueRange3 |
---|---|---|---|---|
1 | 1 | 100 | 99 | 98 |
1 | 2 | 50 | 49 | 97 |
2 | 4 | 10 | 9 | 8 |
2 | 5 | 20 | 19 | 18 |
I need to use intervalmatch or Can I use Set Analysis into Document?
The sales table is a very big table.
Thank you
Set analysis won't work. You can use intervalmatch or if statements. See attached qvw.
Ok, I can't delete the details, because in this case i have no different fields but in real case I have more fields.
I thought best way is intervalmatch but the problem is moltiply the sales tabel three times to have all data.
This is a very big table. Thank you for the intervalmatch with Key.
hi
i try to solve your problem accoding to your requirment.
hope this helps you.
a:
LOAD * INLINE [
ID1, ID2, Date, Value
1, 1, 41264, 100
1, 2, 41264, 50
1, 1, 41250, 99
1, 2, 41251, 49
1, 1, 40893, 98
1, 2, 40894, 97
2, 4, 41249, 10
2, 5, 41250, 20
2, 4, 41235, 9
2, 5, 41236, 19
2, 4, 40878, 8
2, 5, 40879, 18
];
b:
load ID1&ID2&max(Date) AS Key1,
ID1 AS 1,
ID2 AS 2
Resident a group by ID1,ID2;
LEFT JOIN
load ID1&ID2&Date AS Key1,
Value as Range1
Resident a;
C:
load ID1&ID2&max(Date,2) AS Key2,
ID1 AS 1,
ID2 AS 2
Resident a group by ID1,ID2;
LEFT JOIN
load ID1&ID2&Date AS Key2,
Value as Range2
Resident a;
😧
load ID1&ID2&max(Date,3) AS Key3,
ID1 AS 1,
ID2 AS 2
Resident a group by ID1,ID2;
LEFT JOIN
load ID1&ID2&Date AS Key3,
Value as Range3
Resident a;
drop table a;
hi
the another method from which you can achieve this output.
ID1 | ID2 | Range 1 | Range 2 | Range 3 |
180 | 176 | 221 | ||
1 | 1 | 100 | 99 | 98 |
1 | 2 | 50 | 49 | 97 |
2 | 4 | 10 | 9 | 8 |
2 | 5 | 20 | 19 | 18 |
a:
LOAD * INLINE [
ID1, ID2, Date, Value
1, 1, 41264, 100
1, 2, 41264, 50
1, 1, 41250, 99
1, 2, 41251, 49
1, 1, 40893, 98
1, 2, 40894, 97
2, 4, 41249, 10
2, 5, 41250, 20
2, 4, 41235, 9
2, 5, 41236, 19
2, 4, 40878, 8
2, 5, 40879, 18
];
b:
load ID1,ID2,
max(Date) as Date1
Resident a group by ID1,ID2;
b:
load ID1,ID2,
max(Date,2) as Date2
Resident a group by ID1,ID2;
b:
load ID1,ID2,
max(Date,3) as Date3
Resident a group by ID1,ID2;
//*********************************************************
in straight table---
dimension are ID1 , ID2
expression---
for range1---sum(if(Date=Date1,Value))
for range2---sum(if(Date=Date2,Value))
for range 3--sum(if(Date=Date3,Value))
This case don't repsect the interval range. You don't use the table Header
In straight table I have similar situation
but I have numbers instead of date. and I want to match interval between sum of two numbers. Can you help