Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

adamh2404
New Contributor II

Help using Peek/Previous

Hello,

I have data like:

RecordNoExtension No.Extension
12426-
2-A Smith
3-A Smith
152427-
16-M Adams
17-M Adams
18-M Adams
292427-
30-M Adams
31-M Adams
32-M Adams
462428-
47-C Kelly
502429-
51-L Sypniewska
52-L Sypniewska

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

E.G:

RecordNoExtension No.Extension
12426-
22426A Smith
3

2426

A Smith

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Help using Peek/Previous

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;

3 Replies
MVP
MVP

Re: Help using Peek/Previous

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
Contributor II

Re: Help using Peek/Previous

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;

Highlighted
adamh2404
New Contributor II

Re: Help using Peek/Previous

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!