Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
Creator III
Creator III

Load 1 address of 3 based on latest date

QV11 SR4

I need to select the latest address based on the latest date where one exists eg...

Dateformat DD/MM/YY

CMS DateCMS Address1DSB DateDSB Address1MIMS DateMIMS Address1
03/11/1510 Richmond Road04/05/15126 Muirs Lane09/02/15555 Dunbar Street

I know i can use Date(RangeMax....   to quickly find the latest date of the 3 and

In the example above the CMS Date is the latest so i would use CMS Address1 as the latest Address1 which is fine BUT if CMS Address1 is empty i would then want to take the next available Address1 (DSB in the example above) and so on with the MIMS Address1 if the DSB one is empty.

I can satisfy this logic in a lengthy IF statement but am wondering if there's a better more efficient way to handle this?

Any advice?

1 Solution

Accepted Solutions
avinashelite

Try like this :

combine all your date and address to single table like this

EG

Temp:

LOAD ID,

[CMS Date] as New_Date,

[CMS Address1] as Address

resident

orginal_table;

LOAD ID,

[MIMS Date] as New_Date,

[MIMS Address1] as Address

resident

orginal_table;

LOAD ID,

[DSB Date] as New_Date,

[DSB Address1] as Address

resident

orginal_table;

//then use get the max date like this

result:

LOAD ID,

max(New_Date) as Date,

Address

resident

Temp

where len(trim(Address))>0


drop table Temp;


result table will give you the results that your looking for

View solution in original post

16 Replies
avinashelite

Try like this :

combine all your date and address to single table like this

EG

Temp:

LOAD ID,

[CMS Date] as New_Date,

[CMS Address1] as Address

resident

orginal_table;

LOAD ID,

[MIMS Date] as New_Date,

[MIMS Address1] as Address

resident

orginal_table;

LOAD ID,

[DSB Date] as New_Date,

[DSB Address1] as Address

resident

orginal_table;

//then use get the max date like this

result:

LOAD ID,

max(New_Date) as Date,

Address

resident

Temp

where len(trim(Address))>0


drop table Temp;


result table will give you the results that your looking for

View solution in original post

PradeepReddy
Specialist II
Specialist II

there are only 3 addresses or might change dynamically?

haymarketpaul
Creator III
Creator III
Author

yes maximum 3 addresses

haymarketpaul
Creator III
Creator III
Author

thanks for reply - looks like it might work nicely - i'll do some investigation

avinashelite

NOTE: you need to have key field for this logic (in my example ID )

If you don't have the key column try to generate using the rowno() or autonummber() on the source table

PradeepReddy
Specialist II
Specialist II

see the attachment, it might be helpfull

haymarketpaul
Creator III
Creator III
Author

Thank you - this helps

Digvijay_Singh
Master III
Master III

See if below works, might be simplest but didn't try yet. It will show the latest and pick the one which is not null.

Firstsortedvalue(alt(CMS Addr, DSB Addr, MIMS Addr),-Date)

sbobbyraj
Creator III
Creator III

Hi,

You should load with the script a new table wich will have the needed information.

Here is what you need:


SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='# ##0,00 €;-# ##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';
SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';
SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=0;
SET FirstMonthOfYear=0;
SET CollationLocale='';



TABLE:
LOAD * INLINE [
CMS Date, CMS Address1, DSB Date, DSB Address1, MIMS Date, MIMS Address1
03/11/15, 10 Richmond Road, 04/05/15, 126 Muirs Lane, 09/02/15, 555 Dunbar Street
01/02/2015, test1, 05/05/2017, test2 ,05/05/2000, test3]
;


TABLE_RESULT:
 
LOAD
 
[CMS Date] as Date,
 
[CMS Address1] as Adress,
  'CMS'
as Type,
 
[CMS Date]&' - '&[CMS Address1]&' - '&[DSB Date]&' - '&[DSB Address1]&' - '&[MIMS Date]&' - '&[MIMS Address1] as %KEY
 
RESIDENT
  TABLE
 
where len([CMS Address1])>0;

 
LOAD
 
[DSB Date] as Date,
 
[DSB Address1] as Adress,
  'DSB'
as Type,
 
[CMS Date]&' - '&[CMS Address1]&' - '&[DSB Date]&' - '&[DSB Address1]&' - '&[MIMS Date]&' - '&[MIMS Address1] as %KEY
 
RESIDENT
  TABLE
 
where len([DSB Address1])>0;
 
LOAD
 
[MIMS Date] as Date,
 
[MIMS Address1] as Adress,
  'MIMS'
as Type,
 
[CMS Date]&' - '&[CMS Address1]&' - '&[DSB Date]&' - '&[DSB Address1]&' - '&[MIMS Date]&' - '&[MIMS Address1] as %KEY
 
RESIDENT
  TABLE
 
where len([MIMS Address1])>0;
 
LAST_ADRESS:
LOAD
FirstSortedValue(Date,-Date) as LastDate,
FirstSortedValue(Adress,-Date) as Adress,
FirstSortedValue(Type,-Date) as Type,
%KEY
RESIDENT
TABLE_RESULT
GROUP BY
%KEY
;

DROP TABLE TABLE_RESULT;

LEFT Join (TABLE)
LOAD
LastDate as Date,
Adress,
SubField(%KEY,' - ',1) as [CMS Date],
SubField(%KEY,' - ',2) as [CMS Address1],
SubField(%KEY,' - ',3) as [DSB Date],
SubField(%KEY,' - ',4) as [DSB Address1],
SubField(%KEY,' - ',5) as [MIMS Date],
SubField(%KEY,' - ',6) as [MIMS Address1],
Type
RESIDENT
LAST_ADRESS;

DROP TABLE LAST_ADRESS;

Here is the result:

   

CMS DateCMS Address1DSB DateDSB Address1MIMS DateMIMS Address1DateAdressType
03/11/1510 Richmond Road04/05/15126 Muirs Lane09/02/15555 Dunbar Street03/11/1510 Richmond RoadCMS
01/02/2015test105/05/2017test205/05/2000test305/05/2017test2DSB