How to avoid Loops?

[americanetsMD]

Hello Experts,

I have the following Situation:

KreditorTab:

KreditorNr,

KreditorGroupNr,

....

DebitorTab:

DebitorNr,

DebitorGroupNr,

.....

FactTab:

KreditorNr,

DebitorNr,

Values,

....

NEW TABLE!! - it was created a new table in which are shown the groups that exist in both DebitorTab and KreditorTab. This Table contains the following columns:

commonGroupTab:

DebitorNr,

KreditorNr,

commonGroupNumber

Now i need to join somehow the table commonGroupTab to my FactTab, so that i can later show in my report the difference in value between kreditor and debitor, based on the Group field. Any my try leads to a LOOP or to the result that the commonGroupNumber does not communicate within the tables.

Any Ideas on how could i solve this task?

Thank you very much!

[_Julia_ · Accepted solution]

Hi AmericanetsMD,

my idea is to add a new combined field [KreditorNr|DebitorNr] to the tables where there are both [KreditorNr] and [DebitorNr]  fields (tables [commonGroupTab] and [FactTab]). And then to create a [LinkTable] which will contain all combinations of [ KreditorNr], [DebitorNr] and the new combined field [KreditorNr|DebitorNr].  Then you need to drop [KreditorNr], [DebitorNr] from tables [commonGroupTab] and [FactTab].

So tables [KreditorTab] and [DebitorTab] would be connected with Link Table by [KreditorNr] and [DebitorNr] fields, and tables [commonGroupTab] and [FactTab] would be connected by field [KreditorNr|DebitorNr].

Then you will avoid any loops.

FactTab:
KreditorNr &'|'& DebitorNr as [KreditorNr|DebitorNr],
KreditorNr,
DebitorNr,
Values,
....

commonGroupTab:
KreditorNr &'|'& DebitorNr as [KreditorNr|DebitorNr],
DebitorNr,
KreditorNr,
commonGroupNumber



LinkTable:
LOAD DISTINCT
KreditorNr
FROM KreditorTab;

concatenate
LOAD DISTINCT
DebitorNr
FROM DebitorTab;

concatenate
LOAD DISTINCT
KreditorNr,
DebitorNr,
KreditorNr|DebitorNr,
FROM FactTab;

concatenate
LOAD DISTINCT
KreditorNr,
DebitorNr,
KreditorNr|DebitorNr,
FROM commonGroupTab;

drop fields KreditorNr, DebitorNr from FactTab;
drop fields KreditorNr, DebitorNr from commonGroupTab;

[americanetsMD]

Hi Julia, Thank you for your help, it did help me avoiding the Loop. 🙂

[americanetsMD]

.