last date modified syntax

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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!

[Miguel_Angel_Baeyens]

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

[#5]

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;

[rwunderlich]

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

[#7]

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.....

[rwunderlich]

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

-Rob

[#9]

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!