Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need help trying to figure the correct way to join these two tables for a Debtors ageing report model extracting data from SAGE X3.
First scenario I joined these two tables by INV_0 which shows all doc types. When I test these amounts to show INV_0 it seems to associate to NUMDUD_0 on the other table. Any idea how to join them? What this require any other tables?
Second scenario I left joined the Sinvoice table to the Gaccdudate and found that I can only see the doc types SAINV and SACRN. No GEN01 or REC01 types seem to be appearing or could be found by search box.
With Regards to second scenario , there must be no row keys in first table that have a matching row key in the second table that have Doc type GEN01 or REC01, by the nature of LEFT JOIN on Rows matching Key in Left table are kept.
Try an OUTER JOIN (TABLENAME) and see
First Scenario
If fields on different tables have the same name qlikview will automatically create an association
so either rename the field from First table to match the name in the second table or vice versa
Example
Table1
LOAD *,
INV_0 as NUMDUD_0
FROM wewewefwewfwef;
Table2
LOAD *,
NUMDUD_0
FROM wewewefwewfwef;
or
Example
Table1
LOAD *,
INV_0
FROM wewewefwewfwef;
Table2
LOAD *,
NUMDUD_0 as INV_0
FROM wewewefwewfwef;
With Regards to second scenario , there must be no row keys in first table that have a matching row key in the second table that have Doc type GEN01 or REC01, by the nature of LEFT JOIN on Rows matching Key in Left table are kept.
Try an OUTER JOIN (TABLENAME) and see
This seems to work to keep all document types intact. Thank you. Would you suggest using both your suggestions as I'm still not able to "bind" or associate the NUM_0 and NUMDUD_0 to pull all in. Sure there is not a need for an additional table such as PAYMENTD?
Can you post a sample app? or Excel file with multiple tabs of sample data that replicate your scenario?
Preparing examples for Upload - Reduction and Data Scrambling
Would this be helpful. The QVD's would be too large to upload.
Looks good to me
Please note that sometimes transactions in GACCDUDATE will not have a corresponding transaction in SINVOICE
It is possible to create invoices directly from the general ledger. in that event no transaction will exist in sub ledger. This is why the reports also run off GACCDUDATE which is your open item table.
Sometimes the business will choose to take on outstanding AP and AR transactions as only open items rather than a full take on of the outstanding invoices in sub ledger.
also NUMDUD can sometimes go out of sync in which case the resynchronization tool must be run. Probably safer to use NUM to join the tables