topic Re: Apply map in QlikView

Apply map

upaliwije — Wed, 01 Oct 2014 15:34:15 GMT

Mapsalvage:

Mapping Load CLAIM_N0, SALVAGE From e:\SALVAGE.qvd

(qvd) ;

ALL:

Load *,

ApplyMap(' Mapsalvage',CLAIM_NO, null()) as SALVAGE

Resident MC;

DROP TABLE MC;

I have above syntax which is running well. But In MC table there are some times more than one records with the same CLAIM_NO and when the above script is run SALVAGE amount is recorded against all such records duplicating the SALVAGE amount. I want to record salvage amount only against distinct CLAIM_NO. How can restrict such a thing through apply map. Pls help

Re: Apply map

geert_gelade — Wed, 01 Oct 2014 15:37:43 GMT

maybe "mapping load distinct" can help ?

Re: Apply map

upaliwije — Wed, 01 Oct 2014 15:41:45 GMT

What you say is not clear to me

Re: Apply map

geert_gelade — Wed, 01 Oct 2014 15:49:04 GMT

Sorry, I misunderstood you had duplicates in SALVAGE.qvd.

Can't you load you MC table with distinct values ?

Re: Apply map

upaliwije — Wed, 01 Oct 2014 16:03:17 GMT

MC table cannot be loaded with distinct value

Re: Apply map

jyothish8807 — Wed, 01 Oct 2014 16:10:19 GMT

Hi upali,

Try this:

Mapsalvage:

Mapping Load CLAIM_N0, SALVAGE From e:\SALVAGE.qvd

(qvd) ;

ALL:

Load Distinct CLAIM_NO,

ApplyMap(' Mapsalvage',CLAIM_NO, null()) as SALVAGE

Resident MC;

Regards

Re: Apply map

maxgro — Wed, 01 Oct 2014 18:08:47 GMT

see attachment if you want this

Re: Apply map

Wed, 01 Oct 2014 18:33:42 GMT

Hi,

I just tried the below code but not getting any duplications.

Mapsalvage:

Mapping LOAD * inline [

claim_no,salvage

1,100

2,200

3,300

];

MC:

LOAD * inline [

claim_no

];

Demo:

load

applymap('Mapsalvage',claim_no,'null') as Salvage,

claim_no,

1 as field1

resident MC;

drop table MC;

If possible could you post an example?

Re: Apply map

mark_casselman — Wed, 01 Oct 2014 19:08:39 GMT

Hi,

If you chech your table you will see the duplicate rows. They are not shown in a straight table because all data is the same. But if you add an expression count(claim_no), you will see that there are in fact duplicate rows.

If you want to see them, you will have to add a field that is different for the duplicate rows.

For example add a unique RecNo() to the table.

Demo:

load

applymap('Mapsalvage',claim_no,'null') as Salvage,

claim_no,

1 as field1,

RecNo()

resident MC;

First screenshot : without RecNo, with count

Second screenshot : with RecNo

If you don't want to see the RecNo column, just hide it in the properties - presentation as shown here:

Ok ?

Re: Apply map

MarcoWedel — Wed, 01 Oct 2014 22:14:15 GMT

Hi,

instead of integrating your salvage field into your MC table, I would keep the salvage.qvd as seperate table to circumvent the granularity mismatch:

so instead of:

MC:
LOAD * INLINE [
    CLAIM_NO, field1, field2
    1111, 87587, 98798
    2222, 76987, 34354
    2222, 46543, 32132
    3333, 65765, 32432
];

Mapsalvage:
Mapping LOAD * INLINE [
    CLAIM_NO, SALVAGE
    1111, 1234
    2222, 2345
];
           
ALL:
LOAD *,
    ApplyMap('Mapsalvage',CLAIM_NO, null()) as SALVAGE
Resident MC;
   
DROP TABLE MC;

rather try like:

MC:

LOAD * INLINE [

CLAIM_NO, field1, field2

1111, 87587, 98798

2222, 76987, 34354

2222, 46543, 32132

3333, 65765, 32432

];

tabSalvage:

LOAD * INLINE [

CLAIM_NO, SALVAGE

1111, 1234

2222, 2345

];

hope this helps

regards

Marco

Re: Apply map

upaliwije — Wed, 01 Oct 2014 23:22:44 GMT

Thanks all of u