Re: Interval Match or Set Analysis in document? - Qlik Community

comunico · ‎2013-01-25

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

Gysbert_Wassenaar · ‎2013-01-25

Set analysis won't work. You can use intervalmatch or if statements. See attached qvw.

talk is cheap, supply exceeds demand

comunico · ‎2013-01-25

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.

Report Inappropriate Content · ‎2013-01-25

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;

Report Inappropriate Content · ‎2013-01-25

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))

comunico · ‎2013-01-25

This case don't repsect the interval range. You don't use the table Header

Report Inappropriate Content · ‎2014-07-25

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