Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
To
Resident Table1;
Load PartnerNo,
From,
To
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
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
Thank you for the answer, i will have n dimension in the table not only domizil, in that case?
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
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)
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.2010When 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
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