Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Sort by timestamp and comparison

Hi,

I have some trouble to sort a table by date (timestamp) and compare two columns in a script

I have a table like this :

IdentifierDateTextATextB
101/01/13 12:00:00BC
101/01/13 13:00:00CA
101/01/13 11:00:00AB
201/01/13 12:00:00AABB
201/01/13 14:00:00BBAA

I want to show the data like this :

IdentifierText
1A-B-C-A
2AA-BB-AA
3X-Y-X

To do so, I must load data from a database, sort by date (with timestamp !) and concatenate TextA & TextB in the good order (date) and without double, group by Identifier

I have tried to :

Class_sort:

LOAD

     Identifier,

     TextA,

     TextB,

     Date,

     Resident Table_Temp_From_database ORDER BY Date ASC;

But the "ORDER BY" doesn't seem to work

Then I try to concatenate with concat

LOAD

     Concat(TextA, '-' ),

     Resident Class_sort GROUP BY Identifier;

But as the table is not sort by date the concatenation is not good..

Moreover, my problem is that I must compare if previous.TextB = current.TextA before the concatenation to have (A-B-C-A) and not (A-B-B-C-C-A)... But as my table is not sort by date I don't know how to do so...

Does anybody can help me with this ?

Thank you in advance

1 Solution

Accepted Solutions
MVP
MVP

Re: Sort by timestamp and comparison

Maybe like this:

Set TimestampFormat = 'MM/DD/YY hh:mm:ss';

INPUT:

LOAD Identifier,

     Date,

     TextA,

     TextB

FROM

[http://community.qlik.com/thread/95320]

(html, codepage is 1252, embedded labels, table is @1);

LOAD

     Identifier,

     Concat(TextA, '-', Date ) & '-' & FirstSortedValue(TextB,-Date) as Text

Resident INPUT  GROUP BY Identifier;

2 Replies
MVP
MVP

Re: Sort by timestamp and comparison

Maybe like this:

Set TimestampFormat = 'MM/DD/YY hh:mm:ss';

INPUT:

LOAD Identifier,

     Date,

     TextA,

     TextB

FROM

[http://community.qlik.com/thread/95320]

(html, codepage is 1252, embedded labels, table is @1);

LOAD

     Identifier,

     Concat(TextA, '-', Date ) & '-' & FirstSortedValue(TextB,-Date) as Text

Resident INPUT  GROUP BY Identifier;

Not applicable

Re: Sort by timestamp and comparison

Thank you !