Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thank you !