topic Re: Apply map - where is the bug in QlikView

Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 10:14:47 GMT

Hi everybody,

I've tried the first time to use apply map instead of join statement but it is not working as I thought. The reload works but I don't see in the VBEP table the REQ_Delivery_Date. Do you know where the mistake is?

Here the code

MappingEDDtoVBEP:

Mapping Load %VKANRHD%POSHD as %VBELN%UEPOS,

ZGS_SCM_MONITOR.EARLIEST_DELIV

From

...Pickup_Reliability\QVDs\ZGS_SCM_MONITOR_01.qvd (qvd)

;

VBEP:

Load *,

ApplyMap('MappingEDDtoVBEP',%VBELN%UEPOS) as REQ_Delivery_Date

;

Thank you in advance

Michael

Re: Apply map - where is the bug

rustyfishbones — Thu, 23 Oct 2014 10:19:53 GMT

try removing the Alias on the Mapping Table

Mapping Load %VKANRHD%POSHD ,

ZGS_SCM_MONITOR.EARLIEST_DELIV

From

...Pickup_Reliability\QVDs\ZGS_SCM_MONITOR_01.qvd (qvd)

;

VBEP:

Load *,

ApplyMap('MappingEDDtoVBEP',%VKANRHD%POSHD) as REQ_Delivery_Date

;

Re: Apply map - where is the bug

anbu1984 — Thu, 23 Oct 2014 10:20:48 GMT

From which table are you loading VBEP?

VBEP:

Load *,

ApplyMap('MappingEDDtoVBEP',%VBELN%UEPOS) as REQ_Delivery_Date

;

Re: Apply map - where is the bug

salto — Thu, 23 Oct 2014 10:26:27 GMT

Hello Michael,

ApplyMap('MappingEDDtoVBEP',%VBELN%UEPOS,'Not Found') as REQ_Delivery_Date

Is there a correspondence between the values of the second table and the values of the mapping table? If not, maybe that is the reason to have no values in REQ_Delivery_Date.

Hth.-

Re: Apply map - where is the bug

Thu, 23 Oct 2014 10:46:13 GMT

Hi,

VBEP:

Load *,

ApplyMap('MappingEDDtoVBEP',%VBELN%UEPOS) as REQ_Delivery_Date

;

What is the source for this table,atleast you need to use resident or else uyou can load it from some other table.

Re: Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 11:32:31 GMT

Hi,

Thank you for your feedback. I modified the apply map statement as suggested but without success. Without alias, with from and resident in the mapping table. I also add 'Not found'...

My table VBEP will be loaded at the beginning with the data base model, so it already exists.

Have you further ideas?

Re: Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 11:35:51 GMT

Hello Salto,

what do you mean with correspondanc between the values. The ID is equal but there is no equal date field.

Michael

Re: Apply map - where is the bug

oknotsen — Thu, 23 Oct 2014 11:36:40 GMT

What error are you getting (if any)?

Does %VBELN%UEPOS exist in the source you use for VBEP?

What is the content of that field in VBEP? Is there actually any content?

Does it match the casing in the mapping table?

Re: Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 11:41:11 GMT

yes, %VBELN%UEPOS is the id of the table VBEP

if you mean the content of Req_Delivery_Date, then the column doesn't exists.

Actually with apply map I wanted to avoid (left joins statements). Is it possible or did I misunderstand the function of apply map

Re: Apply map - where is the bug

mrybalko — Thu, 23 Oct 2014 11:44:38 GMT

Could you please provide test qvw?

You can check mapping table consistency by joining it to fact table. The problem may be in columns data types. For example in mapping table dates are strings and in fact tables - numbers.

Re: Apply map - where is the bug

martinpohl — Thu, 23 Oct 2014 11:47:49 GMT

If the applymap always returns "not found" the camparing values seems to to be the same.

load the mappinf table from your first topic with renaming so the tables are in relationship.

Create a listbox with the %VBELN%UEPOS-field

Then you can check if the values are equal or by using one field from the VBEP and the other from your mapping-tables, what are the difference.

Maybe figure of the fields?

SAP is using for example 10 figures for the VBELN

Regards

Re: Apply map - where is the bug

Colin-Albert — Thu, 23 Oct 2014 11:52:59 GMT

I would suggest adding a table name to your mapping table rather than relying on the default table name.

Also there is no need to alias fields in the mapping table, the field names are irrelevant. The apply map function just scans for a match on column1 returning column2 and does not use column names.

To held find the error, add the mapping data to a qualified test table this will allow you to see if your mapping data is correct.

// test table

qualify * ;

MappingEDDtoVBEP:

Load %VKANRHD%POSHD ,

ZGS_SCM_MONITOR.EARLIEST_DELIV

From

...Pickup_Reliability\QVDs\ZGS_SCM_MONITOR_01.qvd (qvd)

;

unqualify * ;

MappingEDDtoVBEP:

Mapping Load %VKANRHD%POSHD ,

ZGS_SCM_MONITOR.EARLIEST_DELIV

From

...Pickup_Reliability\QVDs\ZGS_SCM_MONITOR_01.qvd (qvd)

;

VBEP:

Load *,

ApplyMap('MappingEDDtoVBEP',%VBELN%UEPOS) as REQ_Delivery_Date

...

;

Re: Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 12:13:03 GMT

Hi Colin,

that means even the column name of the id is not relevant. Only relevant is that the values of id are in both tables in the first column and are equal... right?

Re: Apply map - where is the bug

Colin-Albert — Thu, 23 Oct 2014 12:18:10 GMT

Yes, the column names are not used by ApplyMap, a mapping table can only have 2 columns.

Also ApplyMap will only return the first matching value.

So if you mapping table has multiple values for a single ID, only the first match is returned.

If the id value is missing from the mapping table, then you can add a third value as a default

ApplyMap('mapping_table_name', ID, 'No Match')

Re: Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 12:27:45 GMT

The values of the id in both columns are equal but you see there are lot of other columns in both tables. So does it mean that apply map is not working here?

Re: Apply map - where is the bug

salto — Thu, 23 Oct 2014 12:32:07 GMT

Hi,

MappingEDDtoVBEP:

Mapping Load %VKANRHD%POSHD as %VBELN%UEPOS,

ZGS_SCM_MONITOR.EARLIEST_DELIV

From

...Pickup_Reliability\QVDs\ZGS_SCM_MONITOR_01.qvd (qvd)

;

VBEP:

Load *,

ApplyMap('MappingEDDtoVBEP',%VBELN%UEPOS, 'No match') as REQ_Delivery_Date

From [YOUR VBEP TABLE]

;

Hth.

Re: Apply map - where is the bug

Colin-Albert — Thu, 23 Oct 2014 12:34:12 GMT

When you load the data from ZGS_SCM_MONITOR_01.qvd into your mapping table you are only loading two columns into the mapping table. (%VKANRHD%POSHD and ZGS_SCM_MONITOR.EARLIEST_DELIV) The other columns in the QVD are not part of the mapping table.

The apply map function will search the MappingEDDtoVBEP table and return the first value of ZGS_SCM_MONITOR.EARLIEST_DELIV (Column2) where %VBELN%UEPOS in VBEP matches %VKANRHD%POSHD (Column1) in the mapping table.

Re: Apply map - where is the bug

salto — Thu, 23 Oct 2014 12:45:40 GMT

No, the number of columns in both tables is not relevant.

Tha mapping table has always two columns (and is automatically deleted when the script finishes, so you do not need to drop it at the end of the script).

Re: Apply map - where is the bug

maternmi — Thu, 23 Oct 2014 13:28:23 GMT

Hi,

I still doesn't works. Anyway, Thanks to all for the support!

Michael

Re: Apply map - where is the bug

Peter_Cammaert — Thu, 23 Oct 2014 14:12:33 GMT

%VKANRHD%POSHD and %VBELN%UEPOS look like home-grown ID fields. Can you post a screenshot of those two fields presented as listboxes? Mapping Tables are rather picky when it comes to matching values...