topic Re: Mapping, ApplyMap or MapSubstring ? in QlikView

Mapping, ApplyMap or MapSubstring ?

Thu, 06 Dec 2012 15:05:07 GMT

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,

Re: Mapping, ApplyMap or MapSubstring ?

Peter_Cammaert — Thu, 06 Dec 2012 15:11:56 GMT

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

Re: Mapping, ApplyMap or MapSubstring ?

Thu, 06 Dec 2012 15:15:55 GMT

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 ?

Thu, 06 Dec 2012 15:16:02 GMT

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 ?

Gysbert_Wassenaar — Thu, 06 Dec 2012 15:30:43 GMT

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

Re: Mapping, ApplyMap or MapSubstring ?

Peter_Cammaert — Thu, 06 Dec 2012 15:37:04 GMT

Nice one indeed.

Peter

Re: Mapping, ApplyMap or MapSubstring ?

Thu, 06 Dec 2012 15:37:11 GMT

Thanks Gysbert,

Now its my friend hahaha

Best Regards,

Re: Mapping, ApplyMap or MapSubstring ?

Peter_Cammaert — Thu, 06 Dec 2012 15:40:39 GMT

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

Re: Mapping, ApplyMap or MapSubstring ?

alis2063 — Sun, 31 Mar 2019 07:55:35 GMT

U Can preform Mapping load as its faster than join

CorrectData:

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

Data:
Load ApplyMap('CorrectData',NAME,AGE) AS CorrectAge,
NAME;
LOAD * INLINE [
NAME, AGE
JOHN, 20
BILL, 25
PAUL, 30
];