Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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!
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
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;
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
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.....
I don't quite understand. You don't want to include Name as a dimension in the chart? What dimensions are you using?
-Rob
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!