Data Tranformations in Server Connect: Add Column

[famousmag]

Hey guys,

I have seen them but didn’t pay attention to them…

Now… I love them!

But please I need some help here.
I’m building a client statistics, getting data from 3 tables:

• Clients (main) ,
• Orders
• Payments

I successfully:

1. created the basic query pulling all the clients data
2. created a custom query pulling the total order’s amount (if there are any) of each client
3. created a custom query pulling the total payment’s amount (if there are any) of each client
4. Joined the main clients query with the orders query (qr_clientRecords_MainOrders)
5. Joined the main clients query with the payments query (qr_clientRecords_MainPayments)
6. Joined the above two queries (4 & 5)
   Now, I can get all the records of clients that have order or/and payment.

Fine!!!
It would be great if I could add a column on my final query (6) to give me the balance
(total_orders - total_payments)

I just can’t understand how to assign the total_orders or total_payment to the new column (“balance”)

Set to:

1. total_payments -> gives me null

2. qr_clientRecords_OrdersPayments[0].total_payments gives me for all the records the value of the first record

3. qr_clientRecords_OrdersPayments[$index].total_payments -> gives me null

If anybody has an idea please

[Quy]

I assume it is due to this shortcoming of Add Columns.

Data Transformation Add Columns - Can I Have A Complex Equation?

Add columns currently do not support referring to other columns in the same record. Just external expressions or static values. Maybe we can improve that @patrick

[Apple]

You may want to toy around with a Repeat step, change the setting of it to include by default, and then any Set Value you place inside will be appended to the row being iterated

If you didn’t understand feel free to ask for clarification, someone will clarify what I wrote

Edit: Also, know about the .flatten formatter, might help you somewhere in the future

[famousmag]

Nice idea @Apple!
I’m already trying n arrayList approach on this

We’ll see how it goes…

I wish it was just an “add column” doing that but anyway we must go on

[famousmag]

Works like a charm!

@Apple

change the setting of it to include by default

(you obviously meant to set the repeat filter to exclude)

@Apple

Edit: Also, know about the .flatten formatter, might help you somewhere in the future

I always search for new things and I’m open to explore new methods…

Thanks @Apple!!