topic Re: Field matching in App Development

Field matching

americanetsMD — Wed, 06 Sep 2023 13:11:44 GMT

Hello everyone,

I have a little problem in getting two fields matched... in order to explain my issue better, consider the following situation:

I have a fact table, where i have the following fields:

FactTab:

1 | - | a | - |Kredior | 200

2 | - | b | - |Kredior | 100

3 | - | c | - |Kredior | 200

- |22 | - | e |Debitor | 50

- |23 | - | b |Debitor | 50

- |24 | - | b |Debitor | 60

My task is to show the kreditorNr and DebitorNr that are having the same group assigned. Besides, based on the KreditorDebitorFlag , the sales have to be calculated. As result i should get something like:

1 | - | a | - |Kredior | 200 | No Match

2 | - | b | - |Kredior | 100 | Match

3 | - | c | - |Kredior | 200 | No Match

- |22 | - | e |Debitor | 50 | No Match

- |23 | - | b |Debitor | 50 | Match

- |24 | - | b |Debitor | 60 | Match

Or maybe is there any other way, possibility to get a desired Pivot Table that would look like:

·CommonGroup

°KreditorNr

°DebitorNr | Kreditorsales | Debitorsales

______________________100___________110___________

·b | |

°2 | 100 | -

°23 | - | 50

°24 | - | 60

I will be really thankful if anyone could help me sort this out.

Many Thanks

Re: Field matching

Aurelien_Martinez — Wed, 06 Sep 2023 13:43:50 GMT

Hi,

You can use mapping load and applymap:

_map_kred:

Mapping Load

KreditorGroup

'Match' as Flag

resident data

;

_map_deb:

Mapping Load

DebitorGroup,

'Match' as Flag

resident data

;

data2:

Load

ApplyMap('_map_kred', DebitorGroup, ApplyMap('_map_deb', KreditorGroup, 'No Match')) as GroupMatch

Resident data

;

Drop Table data;

Re: Field matching

americanetsMD — Thu, 07 Sep 2023 07:36:42 GMT

Hi Aurelien, your theory has unfortunately not worked. I get "Match" by every DebitorGroup and KreditorGroup, as if the ApplyMap would not work...

Re: Field matching

Aurelien_Martinez — Thu, 07 Sep 2023 08:22:34 GMT

Hi,

You can change the mapping load:

_map_kred:

Mapping Load

KreditorGroup

'Match' as Flag

resident data

where KreditorGroup <> '-'

;

_map_deb:

Mapping Load

DebitorGroup,

'Match' as Flag

resident data

where DebitorGroup <> '-'

;

Re: Field matching

americanetsMD — Thu, 07 Sep 2023 09:06:47 GMT

Hi Aurelien, the where condition will not work because every group is assigned to a number. This means, every KreditorNr has a KreditorGroup assigned. But KreditorNr cannot be equal to DebitorNr.

Re: Field matching

PrashantSangle — Thu, 07 Sep 2023 10:51:42 GMT

try below

Fact:

Load * inline [

1 | - | a | - |Kredior | 200

2 | - | b | - |Kredior | 100

3 | - | c | - |Kredior | 200

- |22 | - | e |Debitor | 50

- |23 | - | b |Debitor | 50

- |24 | - | b |Debitor | 60

](delimiter is '|');

Test:

Load Distinct KreditorGroup as matchKreditorGroup Resident Fact

where KreditorGroup <>'-'

;

matchgroup:

Load Distinct DebitorGroup,

'match' as flag Resident Fact

where Exists(matchKreditorGroup,DebitorGroup)

and DebitorGroup <>'-'

;

Left Join (Fact)

Load DebitorGroup, flag Resident matchgroup;

Left Join

Load DebitorGroup as KreditorGroup , flag as newflag Resident matchgroup;

Final:

Load KreditorNr,

DebitorNr,

KreditorGroup,

DebitorGroup,

KreditorDebitorFlag,

sales,

flag as dbflag,

newflag as krflag,

if(flag='-' or IsNull(flag),newflag,flag) as flag

Resident Fact;

Drop table Test, matchgroup,Fact;

Regards,

Prashant Sangle