topic Re: Script Help: Calculated field from 6 timestamps? in QlikView

Script Help: Calculated field from 6 timestamps?

richardouellett — Tue, 09 Jun 2015 10:40:11 GMT

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?

Re: Script Help: Calculated field from 6 timestamps?

swuehl — Tue, 09 Jun 2015 15:30:10 GMT

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;

Re: Script Help: Calculated field from 6 timestamps?

richardouellett — Tue, 09 Jun 2015 18:06:43 GMT

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

Re: Script Help: Calculated field from 6 timestamps?

swuehl — Tue, 09 Jun 2015 18:12:10 GMT

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

Re: Script Help: Calculated field from 6 timestamps?

sunny_talwar — Tue, 09 Jun 2015 18:12:43 GMT

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

Re: Script Help: Calculated field from 6 timestamps?

sunny_talwar — Tue, 09 Jun 2015 18:14:02 GMT

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

Re: Script Help: Calculated field from 6 timestamps?

richardouellett — Tue, 09 Jun 2015 18:23:25 GMT

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.

Re: Script Help: Calculated field from 6 timestamps?

swuehl — Tue, 09 Jun 2015 18:33:46 GMT

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.

Re: Script Help: Calculated field from 6 timestamps?

richardouellett — Tue, 09 Jun 2015 23:47:28 GMT

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.

Re: Script Help: Calculated field from 6 timestamps?

rwunderlich — Tue, 09 Jun 2015 23:55:51 GMT

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

Re: Script Help: Calculated field from 6 timestamps?

richardouellett — Wed, 10 Jun 2015 11:15:10 GMT

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!

Re: Script Help: Calculated field from 6 timestamps?

MayilVahanan — Wed, 10 Jun 2015 12:34:41 GMT

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‌.

Re: Script Help: Calculated field from 6 timestamps?

richardouellett — Wed, 10 Jun 2015 12:52:45 GMT

Thanks so much Mayil, very helpful