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?

[richardouellett]

Rob thank you for the nudge and I apologize swuehl for not putting the results in the table box originally when viewing the results; the newly created order field is ordered correctly and I have the results I originally sought.  I must admit that I am not sure how or where the ordering occurred and suspect the magic takes place in the

CrossTable (Field, Time) LOAD * RESIDENT DATA; 

Line of code.  I believe that “Field” takes into account the actual fields A,B,C,D,E,F in the table Data and orders/ranks them with the above line to create the “Ordered” field.  Can you confirm if this is how this works?

Now that I have the “Ordered” field created, I am uncertain how to incorporate this into my existing script where the existing “data” table has an additional 35 fields of information besides the 6 time-stamp fields.  Additionally it would also be good to delete/eliminate the fields A,B,C,D,E,F since they are only needed temporarily to create the “Ordered” field. Can you offer some suggestions? Many thanks!

[MayilVahanan]

Hi

I am not sure how or where the ordering occurred and suspect the magic takes:

LEFT JOIN (DATA)

LOAD

  ID,

  Concat(Field, '-',Time) as Order  // This part calculate order. Field (A,B, C, D,etc) is concatenate based on "Time"                                                        from earliest to latest .

Resident X

Group BY ID;

I am uncertain how to incorporate this into my existing script where the existing “data” table has an additional 35 fields of information besides the 6 time-stamp fields.

    You can takes only Unique field (Primary Key Field) along with six timestamp using resident table. And join the orders with your original table based on Primary Key;

Additionally it would also be good to delete/eliminate the fields A,B,C,D,E,F since they are only needed temporarily to create the “Ordered” field. Can you offer some suggestions?

     You can drop the fields from original table. Like

     Drop fields x,y from TableName;

Any suggestion swuehl‌ and rwunderlich‌.

[richardouellett]

Thanks so much Mayil, very helpful