Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Mapping, ApplyMap or MapSubstring ?

Hello all,

I´m with a doubt about a "fiel update".

For example, I have a table:

AGES:

LOAD * INLINE [
    NAME, AGE
    JOHN, 20
    BILL, 25
    PAUL, 30
]
;

 

CORRECT:

LOAD * INLINE [
    NAME, AGE
    BILL, 55
]
;

As result, I want this:

NAME, AGE

JOHN, 20

BILL, 55

PAUL, 30.

What better way to do this ?

Best Regars,

1 Solution

Accepted Solutions

Re: Mapping, ApplyMap or MapSubstring ?

Applymap is your friend here I think:

CORRECT:

Mapping LOAD * INLINE [
    NAME, AGE
    BILL, 55
]
;

AGES:

LOAD NAME, applymap('CORRECT',NAME,AGE) as AGE;

LOAD * INLINE [
    NAME, AGE
    JOHN, 20
    BILL, 25
    PAUL, 30
]
;

 


talk is cheap, supply exceeds demand
7 Replies

Re: Mapping, ApplyMap or MapSubstring ?

Just one of multiple ways to do this. Add the following to the end of your script:

CORRECTED_AGES:

LOAD * RESIDENT CORRECT;

CONCATENATE (CORRECTED_AGES)

LOAD * RESIDENT AGES

WHERE NOT EXISTS(NAME);

DROP Table AGES;

RENAME Table CORRECTED_AGES TO AGES;

Best,

Peter

Not applicable

Re: Mapping, ApplyMap or MapSubstring ?

Dear Peter,

I can´t use Resident in this case because I have millions rows.

For this i need a function.

Tks,

Not applicable

Re: Mapping, ApplyMap or MapSubstring ?

Dear Peter,

I can´t use Resident in this case because I have millions rows.

For this i need a function.

Tks,

Re: Mapping, ApplyMap or MapSubstring ?

Applymap is your friend here I think:

CORRECT:

Mapping LOAD * INLINE [
    NAME, AGE
    BILL, 55
]
;

AGES:

LOAD NAME, applymap('CORRECT',NAME,AGE) as AGE;

LOAD * INLINE [
    NAME, AGE
    JOHN, 20
    BILL, 25
    PAUL, 30
]
;

 


talk is cheap, supply exceeds demand

Re: Mapping, ApplyMap or MapSubstring ?

Nice one indeed.

Peter

Not applicable

Re: Mapping, ApplyMap or MapSubstring ?

Thanks Gysbert,

Now its my friend hahaha

Best Regards,

Re: Mapping, ApplyMap or MapSubstring ?

As a bonus: keep in mind that a PRECEDING LOAD uses resident tables as well. They're just not that visible.

As a 2nd bonus: ApplyMap is way faster than exists(), especially with lots of data.

Peter

Community Browser