Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create New Fields Using For Next

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

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

3 Replies
sunny_talwar

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.

Anonymous
Not applicable
Author

Thank you Sunny; i just discovered it is not a unique identifier

sunny_talwar

You can create your own Unique Identifier using RowNo() or RecNo() if that would make sense....