Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
americanetsMD
Contributor III
Contributor III

How to avoid Loops?

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!

Labels (1)
1 Solution

Accepted Solutions
_Julia_
Contributor II
Contributor II

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;

 

View solution in original post

3 Replies
_Julia_
Contributor II
Contributor II

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
Contributor III
Contributor III
Author

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

americanetsMD
Contributor III
Contributor III
Author

.