topic how do i join this? in QlikView

how do i join this?

amien — Tue, 09 Feb 2010 21:54:55 GMT

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
];

how do i join this?

prieper — Tue, 09 Feb 2010 22:12:29 GMT

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

how do i join this?

amien — Tue, 09 Feb 2010 22:54:53 GMT

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?

how do i join this?

prieper — Wed, 10 Feb 2010 17:02:54 GMT

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