Applymap not correctly mapping, DateBridge

[WH_Aqua]

Hello all,

I wanted to ask the community for help in my issue with the Applymap() function. I am using it in a DateBridge creation for the Canonical Date. I've tried to use examples from the web and the community and I think I am following them correctly, but it still does not map as it should.

The main post I am using as an example is this: Canonical Date 

Could anybody point me in the right direction?

EDIT: I added sample data for the tables used in analysis.

The data model is available on the screen attached and the code is as follows:

DATA_REJESTRACJI_map:
mapping load NUMER_KONTRAHENTA, DATA_REJESTRACJI Resident KONTRAHENCI_DANE;

DATA_TRANSAKCJI_map:
mapping load PELNY_NR_FAKTURY, DATA_TRANSAKCJI Resident SPRZEDAZ_DANE;

[DateBridge]:
Load
PELNY_NR_FAKTURY, //NUMER_KONTRAHENTA,
Date(Applymap('DATA_REJESTRACJI_map', NUMER_KONTRAHENTA,Null()), 'YYYY-MM-DD') as DATA_KANONICZNA,
'Rejestracja' as DateType
Resident SPRZEDAZ_DANE;

Load
PELNY_NR_FAKTURY, //NUMER_KONTRAHENTA,
Date(DATA_TRANSAKCJI, 'YYYY-MM-DD') as DATA_KANONICZNA,
'Transakcja' as DateType
Resident SPRZEDAZ_DANE;

[aapurva09]

Hi,

I noticed, the ID field used in the Applymap table (DateBridge) is NUMER_KONTRAHENTA which is not present in the table but in Mapping table DATA_REJESTRACJI_map.

According to the Applymap functionality, the id which is used in the Applymap table should be the ID available in the same table. So, in your case, changing NUMER_KONTRAHENTA  to PELNY_NR_FAKTURY should work.

[DateBridge]:
Load
PELNY_NR_FAKTURY, //NUMER_KONTRAHENTA,
Date(Applymap('DATA_REJESTRACJI_map', PELNY_NR_FAKTURY,Null()), 'YYYY-MM-DD') as DATA_KANONICZNA,
'Rejestracja' as DateType
Resident SPRZEDAZ_DANE;

Thanks

Apurva

[WH_Aqua]

Hello Apurva,

First of all, thanks for answering.

Yes, that might be a problem, however the field DATA_REJESTRACJI which I need to map is in the different table than the PELNY_NR_FAKTURY field. You can see that better on the Data Model screen.

I can't quite get it working.

[WH_Aqua]

Sorry for the late answer, but you were right Aapurva.

Changing NUMER_KONTRAHENTA  to PELNY_NR_FAKTURY was necessary, but in order to solve the issue I also had to play with the data, so that the PELNY_NR_FAKTURY, DATA_TRANSAKCJI and DATA_REJESTRACJI would be all present in one table.

So after all, I did not use Applymap here, but I used it to transfer data between my tables:
[mapping_temp]:
mapping load NUMER_KONTRAHENTA, DATA_REJESTRACJI Resident KONTRAHENCI_DANE;
Drop field DATA_REJESTRACJI from KONTRAHENCI_DANE;

And then in the Load of table SPRZEDAZ_DANE:
ApplyMap('mapping_temp', NUMER_KONTRAHENTA, Null()) AS DATA_REJESTRACJI,

Thanks for help.

The updated code looks as follows:

DATA_REJESTRACJI_map:
mapping load distinct PELNY_NR_FAKTURY, DATA_REJESTRACJI Resident SPRZEDAZ_DANE;

DATA_TRANSAKCJI_map:
mapping load distinct PELNY_NR_FAKTURY, DATA_TRANSAKCJI Resident SPRZEDAZ_DANE;

[DateBridge]:
Load
//NUMER_KONTRAHENTA,
PELNY_NR_FAKTURY,
Date(DATA_REJESTRACJI, 'YYYY-MM-DD') as DATA_KANONICZNA,
'Rejestracja' as DateType
Resident SPRZEDAZ_DANE;

Load
//NUMER_KONTRAHENTA,
PELNY_NR_FAKTURY,
Date(DATA_TRANSAKCJI, 'YYYY-MM-DD') as DATA_KANONICZNA,
'Transakcja' as DateType
Resident SPRZEDAZ_DANE;