Help using Peek/Previous

[adamh2404]

Hello,

I have data like:

RecordNo	Extension No.	Extension
1	2426	-
2	-	A Smith
3	-	A Smith
15	2427	-
16	-	M Adams
17	-	M Adams
18	-	M Adams
29	2427	-
30	-	M Adams
31	-	M Adams
32	-	M Adams
46	2428	-
47	-	C Kelly
50	2429	-
51	-	L Sypniewska
52	-	L Sypniewska

I need to populate "Extension No." with the number from the previous row.

E.G:

RecordNo	Extension No.	Extension
1	2426	-
2	2426	A Smith
3	2426	A Smith

Have tried some peek/previous logic but with no success. Any help is appreciated.

[sunny_talwar · Accepted solution]

May be this

LOAD RecordNo,

     If(Len(Trim([Extension No.])) = 0, Peek('NewExtNo'), [Extension No.]) as NewExtNo,

     If(Len(Trim([Extension])) = 0, Peek('NewExt'), Extension) as NewExt

Resident Fact

Order By RecordNo;

DROP Table Fact;

RENAME Field NewExtNo to [Extension No.];

RENAME Field NewExt to Extension;

[ift_isabelle]

TEST:

Load * Inline [

    RecordNo,ExtensionNR,Extension

    1,2426,-

    2,-,A Smith

    3,-,A Smith

    15,2427,-

    16,-,M Adams

    17,-,M Adams

    18,-,M Adams

    29,2427,-

    30,-,M Adams

    31,-,M Adams

    32,-,M Adams

    46,2428,-

    47,-,C Kelly

    50,2429,-

    51,-,L Sypniewska

    52,-,L Sypniewska

    ];

mapTeST:

Mapping LOAD

  Extension,

     IF(WildMatch(ExtensionNR, '-'), Previous(ExtensionNR), ExtensionNR)

Resident TEST

Where IF(WildMatch(ExtensionNR, '-'), Previous(ExtensionNR), ExtensionNR) <>'-';   

NoConcatenate

TEST:

LOAD RecordNo,

  IF(WildMatch(ExtensionNR, '-'), Applymap('mapTeST', Extension), ExtensionNR) as ExtensionNR,

  Extension

Resident TEST;

Drop table TEST;

[adamh2404]

Hi,

Yes that works. I had something very similar however within the peek I was wrongly using "Peek("Extension No.")" rather than using peek on the new field.

Many thanks!