topic Re: last date modified syntax in QlikView

last date modified syntax

Fri, 13 Jan 2012 13:48:19 GMT

Hi everyone,

i have a table with this kind of information:

1 msantiago 12/05/2001 NewYork online

2 msantiago 12/06/2001 NewYork offline

3 msantiago 12/07/2001 Boston online

and i need to find the last status of a person in every city, so i need a table like this:

2 msantiago 12/06/2001 NewYork offline

3 msantiago 12/07/2001 Boston online

without the line:

1 msantiago 12/05/2001 NewYork online

the problem is i don't know the syntax i have to use.....

thanks

gerardo

Re: last date modified syntax

Miguel_Angel_Baeyens — Fri, 13 Jan 2012 14:49:39 GMT

Hi Gerardo,

Check the following script. DataFromSource is the original data, coming from the source (database, excel file, etc). There are no transformations here. DataTemp checks for the highest value in date in the same city, and stores this value in a new field named "DateMax". Finally, DataFinal loads all records where its original Date field equals to the highest date for that city.

DataFromSource:
LOAD * INLINE [
Id, Name, Date, City, Status
1, msantiago, 12/05/2001, NewYork, online
2, msantiago, 12/06/2001, NewYork, offline
3, msantiago, 12/07/2001, Boston, online
];
DataTemp:
LOAD *,
     If(City = Previous(City), Date(RangeMax(Date, Peek('DateMax'))), Date) AS DateMax
RESIDENT DataFromSource
ORDER BY City, Name ASC, Date DESC;
DataFinal:
NOCONCATENATE LOAD *
RESIDENT DataTemp
WHERE Date = DateMax;
DROP TABLES DataFromSource, DataTemp;

Hope that helps.

Miguel

Re: last date modified syntax

Fri, 13 Jan 2012 16:47:04 GMT

hI Miguel, i think im doing something wrong because there are script errors:

i change city by city_id and name by email.

Field not found

SUBSCRIPTIONS_TEMP:

LOAD *,

If(city_id = Previous(city_id), date(RangeMax(modified, Peek('DateMax'))), modified) AS DateMax

RESIDENT SUBSCRIPTIONS

ORDER BY city_id, email ASC, modified DESC

and

Table not found

SUBSCRIPTIONS_FINAL:

NOCONCATENATE LOAD *

RESIDENT SUBSCRIPTIONS_TEMP

WHERE modified = DateMax

any idea?

the whole script is this:

LOAD

Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_GENERACION_SUBSCRIPTIONS,

city_id,

created,

email,

id as subscriptions_id,

is_voluntary,

is_subscribed,

modified,

user_id

FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

SUBSCRIPTIONS_TEMP:

LOAD *,

If(city_id = Previous(city_id), date(RangeMax(modified, Peek('DateMax'))), modified) AS DateMax

RESIDENT SUBSCRIPTIONS

ORDER BY city_id, email ASC, modified DESC;

SUBSCRIPTIONS_FINAL:

NOCONCATENATE LOAD *

RESIDENT SUBSCRIPTIONS_TEMP

WHERE modified = DateMax;

DROP TABLES SUBSCRIPTIONS, SUBSCRIPTIONS_TEMP;

thank you!

Re: last date modified syntax

Miguel_Angel_Baeyens — Fri, 13 Jan 2012 16:54:35 GMT

Hi Gerardo,

The script you posted starts with a LOAD. Add a label "SUBSCRIPTIONS:" (uppercase) before the LOAD line to make sure the RESIDENT will take this name. If QlikView says table not found is likely because the first LOAD in your code above is labelled anyway but "SUBSCRIPTIONS".

Hope that helps.

Miguel

Re: last date modified syntax

Fri, 13 Jan 2012 17:03:04 GMT

sorry miguel....de label "subscriptions" is there...

again:

SUBSCRIPTIONS:

LOAD

Year(created) & Num(Month(created),'00') & Num(Day(created),'00') as %Key_GENERACION_SUBSCRIPTIONS,

city_id,

created,

email,

id as subscriptions_id,

is_voluntary,

is_subscribed,

modified,

user_id

FROM [..\QVD\SUBSCRIPTIONS.QVD] (qvd);

SUBSCRIPTIONS_TEMP:

LOAD *,

If(city_id = Previous(city_id), date(RangeMax(modified, Peek('DateMax'))), modified) AS DateMax

RESIDENT SUBSCRIPTIONS

ORDER BY city_id, email ASC, modified DESC;

SUBSCRIPTIONS_FINAL:

NOCONCATENATE LOAD *

RESIDENT SUBSCRIPTIONS_TEMP

WHERE modified = DateMax;

DROP TABLES SUBSCRIPTIONS, SUBSCRIPTIONS_TEMP;

last date modified syntax

rwunderlich — Fri, 13 Jan 2012 17:04:51 GMT

If you don't need a data table, but just a chart, you can do it in a straight table chart.

Dimension:

Name

City

Expression:

=FirstSortedValue(Status, -Date)

If you want to include the Date in the chart as well add:

=date(max(Date))

-Rob

http://robwunderlich.com

last date modified syntax

Fri, 13 Jan 2012 18:52:04 GMT

Rob, that you wrote could be help but when i take out the Name the expressions fail...i mean...the values are wrong..

is there any way to include the Name in the expression?

Thanks for any advice.....

last date modified syntax

rwunderlich — Sat, 14 Jan 2012 00:13:23 GMT

I don't quite understand. You don't want to include Name as a dimension in the chart? What dimensions are you using?

-Rob

last date modified syntax

Sat, 14 Jan 2012 12:31:07 GMT

because if i want to know how many people are on-line in each city i can't.

The name dimension in the chart give me the details but the most important value is the number of people by city who is online.

is it clear?

thanks!