Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
MindaugasBacius
Valued Contributor II

Cannot properly connect data. Set analysis issue

Hei

There are two tables.

First table:

AmountDateKey
1002016-01-301
2002016-02-281
3002016-03-312
502016-04-302

Second table:

KeyDate2Value
12016-03-3010
12016-03-3120
12016-04-0130
22016-03-3015
22016-03-3125
22016-04-0135

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

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

Please find the qvw.

Thank you!

Tags (1)
1 Solution

Accepted Solutions

Re: Cannot properly connect data. Set analysis issue

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

17 Replies
MindaugasBacius
Valued Contributor II

Re: Cannot properly connect data. Set analysis issue

  1. I am now thinking is it even possible to make it work with Set analysis?
  2. Maybe the data structure have to be changed?
briancopple
Contributor II

Re: Cannot properly connect data. Set analysis issue

HI

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

santiago_respane
Valued Contributor

Re: Cannot properly connect data. Set analysis issue

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

what is expected output?

MindaugasBacius
Valued Contributor II

Re: Cannot properly connect data. Set analysis issue

The result:

AmountDateKeyValueAggr
1002016-01-301

20

2000
2002016-02-281204000
3002016-03-312257500
Total13500
MindaugasBacius
Valued Contributor II

Re: Cannot properly connect data. Set analysis issue

santiago.respane

  1. The Date and Date2 shouldn't be connected.
  2. The result:
AmountDateKeyValueAggr
1002016-01-301

20

2000
2002016-02-281204000
3002016-03-312257500
Total13500
manucamon
Valued Contributor III

Re: Cannot properly connect data. Set analysis issue

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!

manucamon
Valued Contributor III

Re: Cannot properly connect data. Set analysis issue

Hi Mindaugas:

See attached file plz.

Regards.

Re: Cannot properly connect data. Set analysis issue

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

Community Browser