Data transformation question

[mikegrattan]

I've got a scenario where I need to import Kronos timecard data.  There are multiple fields for one employee's timstamp record, where the job type and hours worked can have up to ten entries each, in separate columns/fields.  Here's a snapshot of the data load editor:

CKTIMEFL:

LOAD TIEMPN & TITIME as EmpIDTimestampKey,

  TIEMPN as EmpID,

    TITIME as [Timestamp],

    TISEC2 as Crew,

    TISEC4 as WorkArea,

    TIPC01 as PayCode1,

    TIPC02 as PayCode2,

    TIPC03 as PayCode3,

    TIPC04 as PayCode4,

    TIPC05 as PayCode5,

    TIPC06 as PayCode6,

    TIPC07 as PayCode7,

    TIPC08 as PayCode8,

    TIPC09 as PayCode9,

    TIPC10 as PayCode10,

    TIHR01 as Hours1,

    TIHR02 as Hours2,

    TIHR03 as Hours3,

    TIHR04 as Hours4,

    TIHR05 as Hours5,

    TIHR06 as Hours6,

    TIHR07 as Hours7,

    TIHR08 as Hours8,

    TIHR09 as Hours9,

    TIHR10 as Hours10;

SQL SELECT * FROM ICEBERG.CLOCFILE00.CKTIMEFL

WHERE TISEC2 = '2000';

My goal is to have separate employee timestamp records for each Paycode and Hours record.  So, instead of this:

I would have something like this:

So, basically, it would be like using CROSSTABLE to unpivot my data.  But, I can't seem to get that function to do what I want, possibly because there are too many fields and there is possibly some ambiguity to the columns and it's not exactly the definition of a crosstable.  But, the essential goal is the same; I want to take the values in Paycode1, Paycode2, Paycode3, etc., and put those into new records along with the corresponding Hours1, Hours2, Hours3.

Anybody got any suggestions?

Thanks!

Mike

[akirkpatrick]

Hi Mike,

It look like your issue is in your DB query statement.  I've worked extensively with Kronos, please let me know if you have additional questions.

Thanks,

Alex