Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
lbuedi92
Contributor III
Contributor III

Distinct Applymap

Dear All,

I got a mapping Table with 'Object Number' and 'Order Income':

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1;

this should be mapped to the Facttable

Fact:

load

*,

  ApplyMap('Map_OrderIncome',Object.Number,'') as Order.Income //

Resident

Fact_temp;

The result is as follows:

Sales Number     Object Number    (i.e.)  Document Number     Order Income

1234                    1234_1                         123456789                    1.000

1234                    1234_2                         123456788                    1.000

1234                    1234_2                         123456788                    1.000

...

But the result should be

Sales Number     Object Number    (i.e.)  Document Number     Order Income

1234                    1234_1                         123456789                    1.000

1234                    1234_2                         123456788                    1.000

1234                    1234_2                         123456788                       -

...

I want to have just one record per Object Number in my Table. Is this possible?

Thanks in advance

Lukas

1 Solution

Accepted Solutions
avinashelite

Try this

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1;

this should be mapped to the Facttable

Fact:

LOAD *,

if(Sales Number=peek(Sales Number) and Object Number=peek(Object Number),'',Order Income) as New_Order_Income;

load

*,

  ApplyMap('Map_OrderIncome',Object.Number,'') as Order.Income //

Resident

Fact_temp;

View solution in original post

10 Replies
Kush
MVP
MVP

How would you decide that to which record I need to do apply map? Please provide more information

lbuedi92
Contributor III
Contributor III
Author

Doesn't really matter to me. The first sorted value (Object Number) would be ok. It is just so I don't have multiple Order Income values if the same Object Number is recorded more than once.

Is that the information you neede?

Best regards

jaumecf23
Creator III
Creator III

Hi,

You can do something like this when you load the table for the ApplyMap:

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1

WHERE SubField(Object Number,'_',2) = 1;

In this way you will only load the Object Mumber values like this *_1. Then in this way when in the applymap will try to search for a value different from *_1 a null will be returned.

avinashelite

Try this

Map_OrderIncome:

mapping load Distinct

Object.Number,

Order.Income

Resident

CE1;

this should be mapped to the Facttable

Fact:

LOAD *,

if(Sales Number=peek(Sales Number) and Object Number=peek(Object Number),'',Order Income) as New_Order_Income;

load

*,

  ApplyMap('Map_OrderIncome',Object.Number,'') as Order.Income //

Resident

Fact_temp;

View solution in original post

lbuedi92
Contributor III
Contributor III
Author

Thanks that worked for me.

How do the peek function and the load statement within the load statement work in that context?

Best regards

Lukas

lbuedi92
Contributor III
Contributor III
Author

There are too many Object Numbers per Sales Number and they can change from time to time. But thank you!

avinashelite

Ha ha ok let me explain , actually we don't have any option in the applymap() function to handle this scenario so I took a different route .


1. peek function - will give the last loaded value of the field

2. Insted of creating resident load  table I took Presiding load approach to reduce the script statement


if(Sales Number=peek(Sales Number) and Object Number=peek(Object Number),'',Order Income) as New_Order_Income;


Sales Number=peek(Sales Number) and Object Number=peek(Object Number)

here Sales Number and Object Number I comparing with the last loaded value and if both are same then I am forcefully making it as null i.e for the first occurrence it will give the value for rest it will give null ...which is your requirement


Hope  this will help you to understand 

lbuedi92
Contributor III
Contributor III
Author

Oh the preceding load actually would reduce my resident loads tremendously!

And I will keep in mind the peek function

avinashelite

make a note preceding load does not work with group by ......