Splitting values of two columns and creating a new record

[Deborah Najm]

I am working with XBase backend data structures that cannot be changed. The
application is a data entry system, that is capturing the date a page was
entered and a unique id for the page. I can write a query to get to this
point

Sample Data
Query1 FLDNAME VALUE PAGE OCCURRENCE USER
TRDT01 10-APR-2006 1 1 X00000
TRDT01 10-APR-2006 1 1 X00001
TRID01 6 1 1 X00001
TRID01 1 1 1 X00000
TRDT01 12-APR-2006 10 1 X00000
TRID01 15 10 1 X00000
TRID01 19 10 2 X00000
TRDT01 12-APR-2006 10 2 X00000
TRDT01 12-APR-2006 11 1 X00000
TRID01 16 11 1 X00000
TRID01 20 11 2 X00000
TRDT01 12-APR-2006 11 2 X00000
TRID01 21 11 3 X00000
TRDT01 12-APR-2006 11 3 X00000
TRDT01 12-APR-2006 12 1 X00000
TRID01 18 12 1 X00000
TRID01 17 13 1 X00000
TRDT01 12-APR-2006 13 1 X00000
TRID01 22 13 2 X00000
TRDT01 12-APR-2006 13 2 X00000
TRDT02 10-APR-2006 2 1 X00001
TRDT02 10-APR-2006 2 1 X00000


What I need is to do some sort of union to get a table that would look like
this:

TRID TRDATE PAGE USER
1 10-APR-2006 1 X00000
6 10-APR-2006 1 X00001
15 12-APR-226 10 X00000


Any help would be greatly appreciated.

 

[Tom Ellison]

Dear Deborah:

Here's some thoughts on this.

Your TRID value seems to be the numeric value of the last 2 digits of
FLDNAME. You could try CInt(Right(FLDNAME, 2)) for this. This assumes the
last two characters in FLDNAME are always digits. It will error if they are
not. I recommend you try this as a first step so that, if it fails, you'll
not have far to go to find out why.

SELECT CInt(Right(FLDNAME, 2)) AS TRID
FROM [Sample Data]

For TRDate it looks like you want a date from the VALUE column. For TRID =
1 there are some rows without a date, and within those that do have a date I
see 10-APR-2006 but also 12-APR-2006. I'm thinking you want MIN(VALUE) for
this value.

SELECT CInt(Right(FLDNAME, 2)) AS TRID,
MIN(VALUE) AS TRDATE
FROM [Sample Data]
GROUP BY CInt(Right(FLDNAME, 2))

How are we doing so far?

For TRID = 1, I see values for PAGE of 1, 10, 11, 12, and 13. For TRID = 2
I see only the value 2. In your sample results you show 1 for both of
these. I do not understand how to derive that result. Could you explain?

For USER it appears you have the minimum again. Try:

SELECT CInt(Right(FLDNAME, 2)) AS TRID,
MIN(VALUE) AS TRDATE,
MIN(USER) AS USR
FROM [Sample Data]
GROUP BY CInt(Right(FLDNAME, 2))

Please let me know if this helped and if I can be of any further assistance.

Tom Ellison

 

[Deborah Najm]

Hi Tom,

Thank you for your response, however the TRID value is not the last 2
digits. This is sample data, in actuality it will be a foreign key to
another database, completely unrelated to the name of the field.

There should be one TRID and one TRDT for each unique combination of USER,
PAGE and OCCURANCE. Basically the result will render a record count that is
half of the source.

I am putting the source back up, so it is easier to read, and I will expand
the desired results, to make it more clear - your help is greatly
appreciated, I am so stuck!!!

Desired Result:

TRID TRDT PAGE OCCURANCE USER

1 10-APR-2006 1 1 X000006
10-APR-2006 1 1 X00001

15 12-APR-2006 10 1 X00000

16 12-APR-2006 11 1 X00000

17 12-APR-2006 13 1 X00000

18 12-APR-2006 12 1 X00000

19 12-APR-2006 10 2 X00000

20 12-APR-2006 11 2 X00000

21 12-APR-2006 11 3 X00000

22 12-APR-2006 13 2 X00000



FLDNAME VALUE PAGE OCCURRENCE USER

TRDT01 10-APR-2006 1 1 X00000

TRDT01 10-APR-2006 1 1 X00001

TRID01 6 1 1
X00001

TRID01 1 1 1
X00000

TRDT01 12-APR-2006 10 1 X00000

TRID01 15 10 1
X00000

TRID01 19 10 2
X00000

TRDT01 12-APR-2006 10 2 X00000

TRDT01 12-APR-2006 11 1 X00000

TRID01 16 11 1
X00000

TRID01 20 11 2
X00000

TRDT01 12-APR-2006 11 2 X00000

TRID01 21 11 3 X00000

TRDT01 12-APR-2006 11 3 X00000

TRDT01 12-APR-2006 12 1 X00000

TRID01 18 12 1
X00000

TRID01 17 13 1 X00000

TRDT01 12-APR-2006 13 1 X00000

TRID01 22 13 2 X00000

TRDT01 12-APR-2006 13 2 X00000

TRDT02 10-APR-2006 2 1 X00001

TRDT02 10-APR-2006 2 1 X00000

Dear Deborah:

Here's some thoughts on this.

Your TRID value seems to be the numeric value of the last 2 digits of

FLDNAME. You could try CInt(Right(FLDNAME, 2)) for this. This assumes the

last two characters in FLDNAME are always digits. It will error if they
are

not. I recommend you try this as a first step so that, if it fails, you'll

not have far to go to find out why.

SELECT CInt(Right(FLDNAME, 2)) AS TRID

FROM [Sample Data]
For TRDate it looks like you want a date from the VALUE column. For TRID =

1 there are some rows without a date, and within those that do have a date
I

see 10-APR-2006 but also 12-APR-2006. I'm thinking you want MIN(VALUE) for

this value.

SELECT CInt(Right(FLDNAME, 2)) AS TRID,

MIN(VALUE) AS TRDATE

FROM [Sample Data]

GROUP BY CInt(Right(FLDNAME, 2))

How are we doing so far?

For TRID = 1, I see values for PAGE of 1, 10, 11, 12, and 13. For TRID = 2

I see only the value 2. In your sample results you show 1 for both of

these. I do not understand how to derive that result. Could you explain?

For USER it appears you have the minimum again. Try:

SELECT CInt(Right(FLDNAME, 2)) AS TRID,

MIN(VALUE) AS TRDATE,

MIN(USER) AS USR

FROM [Sample Data]

GROUP BY CInt(Right(FLDNAME, 2))

Please let me know if this helped and if I can be of any further
assistance.

Tom Ellison

Dear Deborah:

Here's some thoughts on this.

Your TRID value seems to be the numeric value of the last 2 digits of
FLDNAME. You could try CInt(Right(FLDNAME, 2)) for this. This assumes
the last two characters in FLDNAME are always digits. It will error if
they are not. I recommend you try this as a first step so that, if it
fails, you'll not have far to go to find out why.

SELECT CInt(Right(FLDNAME, 2)) AS TRID
FROM [Sample Data]

For TRDate it looks like you want a date from the VALUE column. For TRID
= 1 there are some rows without a date, and within those that do have a
date I see 10-APR-2006 but also 12-APR-2006. I'm thinking you want
MIN(VALUE) for this value.

SELECT CInt(Right(FLDNAME, 2)) AS TRID,
MIN(VALUE) AS TRDATE
FROM [Sample Data]
GROUP BY CInt(Right(FLDNAME, 2))

How are we doing so far?

For TRID = 1, I see values for PAGE of 1, 10, 11, 12, and 13. For TRID =
2 I see only the value 2. In your sample results you show 1 for both of
these. I do not understand how to derive that result. Could you explain?

For USER it appears you have the minimum again. Try:

SELECT CInt(Right(FLDNAME, 2)) AS TRID,
MIN(VALUE) AS TRDATE,
MIN(USER) AS USR
FROM [Sample Data]
GROUP BY CInt(Right(FLDNAME, 2))

Please let me know if this helped and if I can be of any further
assistance.

Tom Ellison

I am working with XBase backend data structures that cannot be changed.
The application is a data entry system, that is capturing the date a page
was entered and a unique id for the page. I can write a query to get to
this point

Sample Data
Query1 FLDNAME VALUE PAGE OCCURRENCE USER
TRDT01 10-APR-2006 1 1 X00000
TRDT01 10-APR-2006 1 1 X00001
TRID01 6 1 1 X00001
TRID01 1 1 1 X00000
TRDT01 12-APR-2006 10 1 X00000
TRID01 15 10 1 X00000
TRID01 19 10 2 X00000
TRDT01 12-APR-2006 10 2 X00000
TRDT01 12-APR-2006 11 1 X00000
TRID01 16 11 1 X00000
TRID01 20 11 2 X00000
TRDT01 12-APR-2006 11 2 X00000
TRID01 21 11 3 X00000
TRDT01 12-APR-2006 11 3 X00000
TRDT01 12-APR-2006 12 1 X00000
TRID01 18 12 1 X00000
TRID01 17 13 1 X00000
TRDT01 12-APR-2006 13 1 X00000
TRID01 22 13 2 X00000
TRDT01 12-APR-2006 13 2 X00000
TRDT02 10-APR-2006 2 1 X00001
TRDT02 10-APR-2006 2 1 X00000


What I need is to do some sort of union to get a table that would look
like this:

TRID TRDATE PAGE USER
1 10-APR-2006 1 X00000
6 10-APR-2006 1 X00001
15 12-APR-226 10 X00000


Any help would be greatly appreciated.