Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

last date modified syntax

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

8 Replies

Re: last date modified syntax

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

Not applicable

Re: last date modified syntax

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

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

Not applicable

Re: last date modified syntax

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;

MVP & Luminary
MVP & Luminary

last date modified syntax

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

Not applicable

last date modified syntax

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

MVP & Luminary
MVP & Luminary

last date modified syntax

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

-Rob

Not applicable

last date modified syntax

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!