topic Re: Cannot properly connect data. Set analysis issue in QlikView

Cannot properly connect data. Set analysis issue

MindaugasBacius — Wed, 01 Jun 2016 13:12:08 GMT

Hei

There are two tables.

First table:

Amount	Date	Key
100	2016-01-30	1
200	2016-02-28	1
300	2016-03-31	2
50	2016-04-30	2

Second table:

Key	Date2	Value
1	2016-03-30	10
1	2016-03-31	20
1	2016-04-01	30
2	2016-03-30	15
2	2016-03-31	25
2	2016-04-01	35

Pick up all Amount fields where Date <= 2016-03-31.
Find the Date2 = 2016-03-31 and connect Value to the First table.
Make aggregation Amount * Value.

I was struggling to accomplish this task by using Set analysis.

Please find the qvw.

Thank you!

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Wed, 01 Jun 2016 13:43:15 GMT

I am now thinking is it even possible to make it work with Set analysis?
Maybe the data structure have to be changed?

Re: Cannot properly connect data. Set analysis issue

Anonymous — Wed, 01 Jun 2016 13:43:37 GMT

I notice you have two date2 values of 31/03/2016 in table 2 one for key value 1 and the other for key value 2. Would it not be best to make a unique field from key and date in table 1 to associate to the same unique key made up from key and date2 in table 2 and then you should be able to calculate the correct sum of amount*value.

Kindest Regards

Brian

Re: Cannot properly connect data. Set analysis issue

santiago_respane — Wed, 01 Jun 2016 14:30:42 GMT

Hi Mindaugas,

Date and Date2 should not be associated?

In point 2 When you select 2016-03-31 in Date 2 you want all values for the key of first table only or the value for the key/date combination of the first table?

Maybe you need to combine the key and date in both tables and associate data by that composite key only.

Please let me know if this helps.
Kind regards,

Re: Cannot properly connect data. Set analysis issue

Kushal_Chawda — Wed, 01 Jun 2016 14:48:11 GMT

what is expected output?

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Wed, 01 Jun 2016 15:05:05 GMT

The result:

Amount	Date	Key	Value	Aggr
100	2016-01-30	1	20	2000
200	2016-02-28	1	20	4000
300	2016-03-31	2	25	7500
Total				13500

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Wed, 01 Jun 2016 15:10:08 GMT

santiago.respane

The Date and Date2 shouldn't be connected.
The result:

Amount	Date	Key	Value	Aggr
100	2016-01-30	1	20	2000
200	2016-02-28	1	20	4000
300	2016-03-31	2	25	7500
Total				13500

Re: Cannot properly connect data. Set analysis issue

Anonymous — Wed, 01 Jun 2016 15:13:32 GMT

Sum of amount is correct:

SUM({< Date = {"<=$(=date(var_date))"}>}Amount), but you can't show Date field.

Date2 value -> Max({<Date2 = {"<=$(=date(var_date))"}, Key = P({<Date = {"<=$(=date(var_date))"}>})>}Date2)

May be this??

Regards!

Re: Cannot properly connect data. Set analysis issue

Anonymous — Wed, 01 Jun 2016 15:20:21 GMT

Hi Mindaugas:

See attached file plz.

Regards.

Re: Cannot properly connect data. Set analysis issue

Kushal_Chawda — Wed, 01 Jun 2016 15:43:50 GMT

=SUM({<Date={"<=$(=date(var_date))"}>}Amount) * Only({<Date2={"$(=date(var_date))"}>}Value)

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Thu, 02 Jun 2016 06:34:14 GMT

Still cannot get it right.

I have modified the expression to:

=sum(Amount)/only({<Date = {"2016.03.31"}>} Value)

It do not calculate the Total value:

Total sum works while selecting the State:

How should I solve that?

Thank you in advance!

Re: Cannot properly connect data. Set analysis issue

Kushal_Chawda — Thu, 02 Jun 2016 06:42:07 GMT

can you try this

sum(Amount)/Sum({<Date = {"2016.03.31"}>} Total <State>Value)

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Thu, 02 Jun 2016 06:50:16 GMT

Unfortunately:

Re: Cannot properly connect data. Set analysis issue

Kushal_Chawda — Thu, 02 Jun 2016 06:53:17 GMT

change total mode from "Expression total" to "Sum of Rows"

Re: Cannot properly connect data. Set analysis issue

Kushal_Chawda — Thu, 02 Jun 2016 06:59:59 GMT

or try this

sum(Amount)/only({<Date = {"2016.03.31"}>} Total <State>Value)

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Thu, 02 Jun 2016 07:02:00 GMT

kush141087

You're good at this!

Thank you for your time.

Re: Cannot properly connect data. Set analysis issue

Kushal_Chawda — Thu, 02 Jun 2016 07:03:35 GMT

Not a problem

Re: Cannot properly connect data. Set analysis issue

MindaugasBacius — Thu, 02 Jun 2016 07:05:51 GMT

Both expressions return correct answers when changing from Expression Total to Sum of Rows.