Script Help: Calculated field from 6 timestamps?

[richardouellett]

I have 6 timestamp fields in the format of MM/DD/YYYY hh:mm named A, B, C, D, E, F associated with each record/row of a table and would like to create a new field “Order” that sorts these 6 time stamps  in order of sequence from earliest timestamp to latest.  For example, if they all occur in time sequence equivalent to alphabetical order the newly created field would be “ABCDEF”.  If however “C” had an earlier timestamp than “B” the created field would be “ACBDEF”.  Can you please assist and/or suggest a Qlik user group forum that I can post this question for feedback and/or assistance?

[swuehl · Accepted solution]

Maybe like this:

DATA:
LOAD
  recno() as ID,
  Timestamp(today() + RAND()) as A,
  Timestamp(today() + RAND()) as B,
  Timestamp(today() + RAND()) as C,
  Timestamp(today() + RAND()) as D,
  Timestamp(today() + RAND()) as E,
  Timestamp(today() + RAND()) as F
AutoGenerate 10;

X:
CrossTable (Field, Time) LOAD * RESIDENT DATA;

LEFT JOIN (DATA)
LOAD
  ID,
  Concat(Field, '-',Time) as Order
Resident X
Group BY ID;

drop table X;

[richardouellett]

Swuehl

Thank you for the quick response.  A – F needs to be ranked from earliest date-stamp to latest timestamp as a  requirement.  As I indicated in the original post… For example, if they all occur in time sequence equivalent to alphabetical order the newly created field would be “ABCDEF”.  If however “C” had an earlier timestamp than “B” the created field would be “ACBDEF”.

Best

Rick

[swuehl]

So what's wrong with my Order field, running the script above?

[sunny_talwar]

Have you checked the output from swuehl‌ script? It is exactly doing what you asked for I believe:

[sunny_talwar]

You are amazing man... Kudos to your knowledge of QlikView

[richardouellett]

Swuehl

Yes I ran the script with results below.  The time stamps need to be ordered in sequence. so there is a step missing.  For example the 1st row should be ABECFD and not ACEDFB.

[swuehl]

Use a table box or sort all list boxes by load order or ID. You are not looking at values related to the same record.

[richardouellett]

The "Ordered" field needs to be created within the script for each record.  Each record contains 6 timestamps.  The sequence of the 6 timestamps need to be sorted from earliest to latest time stamp and used to create the "Ordered" field.

[rwunderlich]

I think swuehl's answer is correct and you are viewing the data incorrectly. You can't line up a set of listboxes and assume row relationships between them. Look at your data in a tablebox as suggested and see if you get the correct result.

-Rob