topic Re: Set Analysis - Date Expression in QlikView

Set Analysis - Date Expression

pamaxeed — Thu, 12 Jun 2014 12:05:04 GMT

Hi,

i am struggling with dates problem.

I have the following tables as datasource:

Table1:

PartnerNo	Name	LastName	From	To
1	Hans	House	01.01.2011	31.01.2012
1	Hans	House	01.02.2011	31.02.2012


Table2:
PartnerNo	Country	Domizil	From	To
1	CH	CH	01.01.2011	10.01.2011
1	CH	FR	10.01.2011	15.01.2011
1	CH	I	15.01.2011	25.01.2011
1	CH	DE	25.01.2011	03.02.2011

The two tables are connected with the PartnerNo only in my datamodel in qlikview.

I build a Calendar and i am using the interval function two allow selection between date range.

Snippet of script:

DateLink:

Load PartnerNo,

From,

Resident Table1;

Load PartnerNo,

From,

Resident Table2;

and using then the interval function to allow date (day, month, year) selection.

The problem i encounter now is that when i make a month selection, in this case january i want that my chart looks like that:

Name	LastName	Country	Domizil
Hans	House	CH	DE

This means i want to have the Name and LastName from Table 1 and the max(date) from the selected month.

How i can achieve this?

Best regads,

Patric

Re: Set Analysis - Date Expression

Thu, 12 Jun 2014 12:25:33 GMT

Hi Patric

Domizil holds the domizil according to the last date so

only(aggr(only(domizil), max(date))) to get the last domizil should work

you could also use a rowno() function to get an order number if you load with order by "From" and take the max(n)

best regards

Chris

Re: Set Analysis - Date Expression

pamaxeed — Thu, 12 Jun 2014 12:41:22 GMT

Thank you for the answer, i will have n dimension in the table not only domizil, in that case?

Re: Set Analysis - Date Expression

Thu, 12 Jun 2014 12:49:21 GMT

Doesn't matter how many dimensions you have

You need to know the fields you want as reference to assign Domizil (if only Date allows you to assign Domizil you aggr on max(date))

Chris

Re: Set Analysis - Date Expression

Anonymous — Thu, 12 Jun 2014 13:00:22 GMT

Hi,

Try with these expressions,

Only({<From={"=$(Date(max(From),'DD.MM.YYYY'))"}>}Name)

Only({<From={"=$(Date(max(From),'DD.MM.YYYY'))"}>}LastName)

Only({<From={"=$(Date(max(From),'DD.MM.YYYY'))"}>}Country)

Only({<From={"=$(Date(max(From),'DD.MM.YYYY'))"}>}Domizil)

Re: Set Analysis - Date Expression

pamaxeed — Thu, 12 Jun 2014 16:43:31 GMT

Hi again,

i tried to get rid with the date problems, but hmmm....

The problem of this 2 tables is that the time measure is different.for the same partner.

The first table have a monthly "FROM" - "TO" (ex: 01.01.2010-31.01.2010), the second table can have different periods in the same month, like:

- 01.01.2010 - 10.01.2010

- 10.01.2010 - 17.01.2010

- 17.01.2010 - 25.01.2010

- 25.01.2010 - 02.02.2010

When i have a selection scenario where i select the month january 2010, the result should be the data of the table 1 from month january and the Max(From) from the table 2, in the same row (like a JOIN):

Name	LastName	Country	Domizil
Hans	House	CH	DE

Attached my demo application.

Thanks for ur help i am struggling the whole day to find a solution.

Best regards,

Patric

Re: Set Analysis - Date Expression

pamaxeed — Thu, 12 Jun 2014 19:35:03 GMT

The only way i found to solve it is to make a cartesian product between Table1 and Table2 taken as only joining field the PartnerNO and taking as reference dates the monthly one in my interval function.

The expression in the chart is very simply, is there a way to build it via Set Analysis?

if(To >= Valid_from AND To <= Valid_to, only(Domizil)) ?

Attached the sample.

Thanks!

Every day is a new challenge