topic Re: max(date) for each client computer in QlikView

max(date) for each client computer

Thu, 22 Sep 2016 06:51:17 GMT

Good morning everybody,

I hope someone can give me a hint, how I can add an new field to my data model that shows the highest compliance date for a client computer. I found several ideas but I could not find the right solution for my issue...

I import a number of daily built raw-data tables (source is csv) into a .qvd and afterwards load them into my application. My challenge is now to find the highest date that is found in a certain field and add it to a new table that is linked to my data model via ClientID.

The Client ID appears several times with different LastComplianceTime-values (including NULL). I want to keep the values for creating data history.

Now I need a function that produces a new field called "MaxLastComplianceTime" that is filled for each client with the maximum value in the date field "LastComplianceTime". In the example below it would be "08.09.2016" for client D0000015 resp. "20.09.2016" (DD.MM.YYYY) for client D0000016.

rawData:

ClientID	LastComplianceTime
D0000015
D0000015	08.09.2016
D0000015	08.09.2016
D0000015	06.09.2016
D0000015	05.09.2016
D0000016	20.09.2016
D0000016	13.09.2016
D0000016	30.08.2016
D0000016	30.08.2016
D0000016

Desired result:

MaxComplianceTime:

ClientID	MaxLastComplianceTime
D0000015	08.09.2016
D0000016	20.09.2016

I appreciate every approach!

Thanks in advance for your support!

Greetings, Marcus

Re: max(date) for each client computer

m_woolf — Thu, 22 Sep 2016 16:11:15 GMT

join load

ClientID,

Max(LastComplianceTime) as MaxLastComplianceTime

resident Your Table Name

Group By ClientID;

Re: max(date) for each client computer

MarcoWedel — Thu, 22 Sep 2016 23:37:17 GMT

MaxComplianceTime:
LOAD ClientID,
          Date(Max(LastComplianceTime)) as MaxLastComplianceTime

Resident rawData
Group By ClientID;

hope this helps

regards

Marco

Re: max(date) for each client computer

Fri, 23 Sep 2016 07:19:07 GMT

Hello Marco,

wow, it works! Thank you so much!

Greetings, Marcus

Re: max(date) for each client computer

Fri, 23 Sep 2016 07:21:29 GMT

Hello m w,

thank you for your reply! This helps a lot!

Greetings, Marcus

Re: max(date) for each client computer

MarcoWedel — Fri, 23 Sep 2016 19:16:12 GMT

Hi,

glad it worked.

Please close your thread if your question is answered.

Thanks

Regards

Marco