I am trying to convert a Hyperion 8.5 to WebFocus. The original Hyperion report use “concat (case, app_date)” to concatenate case number and app_date to get CaseApp as following.

Case App_Date CaseApp
C000001 11/03/14 C0000011414972800000
C000002 04/18/16 C0000021460937600000

Is any way I can get the same result after I concatenate case number and app_date to CaseApp?

Thank you for your help.

This message has been edited. Last edited by: <Emily McAllister>,

I'm not sure I understand your question. To concatenate two columns in WebFOCUS, you simply use one of two concatenation operators, | or ||. If the app_date column is really a date or date-time column, you'll have to convert it to a string before concatenation.


Creating Reports With WebFOCUS Language > Using Expressions > Creating a Character Expression > Concatenating Character Strings

Thank you. My problem is I can't get the same result from Hyperion such as "C000001141972800000" from "C000001" and "11/03/14".

quote:

141972800000

What is this?

I have no clue either. The original "concat (case, app_date)" from Hyperion generate this.

Thank you.

It looks like it may be the number of seconds elapsed since January 1, 1970 (Epoch Time).

Looks like. But is any way the WebFocus can do the same thing? Thank you.

Make that milliseconds.

Exactly as Squatch suggests -

TABLE FILE CAR
  SUM COMPUTE ENDDATE/HYYMDS = HINPUT(10,'04/18/2016',8,'HYYMDS');
      COMPUTE BEGDATE/HYYMDS = HINPUT(10,'01/01/1970',8,'HYYMDS');
      COMPUTE DIFF/D20c      = HDIFF(ENDDATE,BEGDATE,'DAY','D20') * 24 * 60 * 60 * 1000;
   BY COUNTRY
END

T

Use the document at the following link to understand datetime (and other) functions.

Also checkout FPRINT as you will need it!

T

Thank you very much, I'll take look at it. Thank you for your help.

Thinkiing about this a little more and you may be able to add a define to your synonym something like -

DEFINE CONCATFLD/A24 = SQL.concat(case, app_date);

You would need to check the SQL produced but this should be more efficient than using COMPUTES or DEFINES to convert date(time) field values.

T

Great suggestion!

Thank you for all your help.

Ed,

You can also use the general DB_EXPR function.
See in the documentation:
"Using Functions > Data Source and Decoding Functions > DB_EXPR: Inserting an SQL Expression Into a Request"

quote:

Originally posted by Tony A:
Exactly as Squatch suggests -

TABLE FILE CAR
  SUM COMPUTE ENDDATE/HYYMDS = HINPUT(10,'04/18/2016',8,'HYYMDS');
      COMPUTE BEGDATE/HYYMDS = HINPUT(10,'01/01/1970',8,'HYYMDS');
      COMPUTE DIFF/D20c      = HDIFF(ENDDATE,BEGDATE,'DAY','D20') * 24 * 60 * 60 * 1000;
   BY COUNTRY
END

T

Don't you need to make sure that you're using 01/01/1970 at the UTC time zone? I'm pretty sure that the method above will have issues with DST change-overs and other differences in time zones.

That might be the case, but then this was only a suggestion on a way forward. Ultimately I was not providing the entire solution but a vehicle to attain what was needed.

However, I would abandon this method and go for the SQL.function method instead.

T

quote:

Originally posted by Wep5622:

quote:

Originally posted by Tony A:
Exactly as Squatch suggests -

TABLE FILE CAR
  SUM COMPUTE ENDDATE/HYYMDS = HINPUT(10,'04/18/2016',8,'HYYMDS');
      COMPUTE BEGDATE/HYYMDS = HINPUT(10,'01/01/1970',8,'HYYMDS');
      COMPUTE DIFF/D20c      = HDIFF(ENDDATE,BEGDATE,'DAY','D20') * 24 * 60 * 60 * 1000;
   BY COUNTRY
END

T

Don't you need to make sure that you're using 01/01/1970 at the UTC time zone? I'm pretty sure that the method above will have issues with DST change-overs and other differences in time zones.

Something tells me that the epoch conversion is being combined with the "Case" field to generate a unique identifier. If that's true, then it probably doesn't matter.

And since the "App_Date" is always a date and not a datetime, that's another indicator to me that time is not a factor here, and so time zones wouldn't be either.

Just a little Sherlock Holmes sleuthing on my part (Ahem... I mean "guess"), but I think only EdHou knows for sure.

That's not quite correct I'm afraid.

The results of above code can be different from those generated by Hyperion, namely when the epoch in UTC is on a different date than the one in the local time zone.
That happens every year around the DST change-over if the time difference between UTC and local time is more than 2 or 3 hours, depending on the direction of the change-over and the local time zone offset from UTC.

Even if all that EdHou needs is a unique identifier, the identifiers created when the clock goes back an hour could be duplicates of identifiers created an hour before.

It is unfortunate that WF does not have better time zone support in its datetime format and functions.