Sort by timestamp and comparison

Wed, 09 Oct 2013 15:05:32 GMT

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 :

Identifier	Date	TextA	TextB
1	01/01/13 12:00:00	B	C
1	01/01/13 13:00:00	C	A
1	01/01/13 11:00:00	A	B
2	01/01/13 12:00:00	AA	BB
2	01/01/13 14:00:00	BB	AA

I want to show the data like this :

Identifier	Text
1	A-B-C-A
2	AA-BB-AA
3	X-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

Re: Sort by timestamp and comparison

swuehl — Wed, 09 Oct 2013 15:18:59 GMT

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;

Re: Sort by timestamp and comparison

Thu, 10 Oct 2013 10:02:16 GMT

Thank you !

topic Re: Sort by timestamp and comparison in QlikView

Sort by timestamp and comparison

Re: Sort by timestamp and comparison

Re: Sort by timestamp and comparison