how do i join this?

[amien]

i want this:

1, 20100101, 200 (value is first found in second found)

1, 20100201, 300 (value is second found in second table)

1, 20100301, 100 (value is third found in second table)

1, 20100401, - (no value found in second table)

LOAD * INLINE [
 FileNumber, date
 1, 20100101
 1, 20100201
 1, 20100301
 1, 20100401
];

LEFT JOIN

LOAD * INLINE [
 FileNumber, value
 1, 200
 1, 300
 1, 100
];

[prieper]

If I get this correctly, you wish to assign the value of the first record from Table2 to the first record in Table1, the second record from Table2 to the second record in Table 1 etc. In both tables then you should insert Recordnumber and join them:

<pre>LOAD RecNo() AS Rec, * INLINE [
 FileNumber, date
 1, 20100101
 1, 20100201
 1, 20100301
 1, 20100401];

LEFT JOIN

LOAD RecNo() AS Rec, * INLINE [
 FileNumber, value
 1, 200
 1, 300
 1, 100];

Depending on the data you may replace the first line with a

LOAD MID(date, 6, 1) AS Rec, * INLINE [

HTH
Peter

[amien]

Peter.. Thanks for your fast reply

i'v changed to

IF(Previous(FileNumber) = FileNumber,Peek(Rec)+1,1) AS Rec

this to manage more than one FileNumber.. See attachment.

Whats your opinion on this approach?

[prieper]

If it helps ...
Think that you provide only a sniplet of your data and that most likely real data are a bit more complicated. The problem with the joining is solved, how to parameterize the fields for joining.

Peter