Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
KreditorNr | DebitorNr | KreditorGroup | DebitorGroup | KreditorDebitorFlag | sales
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:
KreditorNr | DebitorNr | KreditorGroup | DebitorGroup | KreditorDebitorFlag | sales | GroupMatch
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
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 <> '-'
;
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;
Hi Aurelien, your theory has unfortunately not worked. I get "Match" by every DebitorGroup and KreditorGroup, as if the ApplyMap would not work...
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 <> '-'
;
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.
try below