Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a dynamic ageing table.
The invoice table contains 13,000 rows
When the below iteration is made, then invoice table load around 3,000,000 rows. I do not know what is happening
I think there is something wrong in the left join- i wish to join the two table using the CollateralNumber
//Derive dynamic ageing field for invoice by invoice
Let [DD First Month] = MonthEnd($(varMinDate));
For x = 1 to 5
Let [Column_Field] = Ceil(Num(MonthEnd(AddMonths([DD First Month],$(x)))));
Let [Column_Header] = Date([Column_Field],'M-YY');
[temp_client_name]:
Load CollateralNumber,
$(Column_Field)-[Invoice Issue Date] as '$(Column_Header)'
Resident Invoice;
Left Join (Invoice) Load * Resident [temp_client_name];
Drop Table [temp_client_name];
Next
Is CollateralNumber a unique identifier field from Invoice table? If it is not, then you are doing many to many join which is bound to multiple in 5 iterations.
Is CollateralNumber a unique identifier field from Invoice table? If it is not, then you are doing many to many join which is bound to multiple in 5 iterations.
Thank you Sunny; i just discovered it is not a unique identifier
You can create your own Unique Identifier using RowNo() or RecNo() if that would make sense....