Restructure Logic for clients with date

[julianhoetzel]

Hi guys, 

I need help regarding the following problem: 

Client_Number	Date	Finance Amount
1	2021-01-01	100
2	2021-01-01	200
3	2021-01-01	300
4	2021-01-01	400
1	2021-01-02	100
3	2021-01-02	300
4	2021-01-02	400
1	2021-01-03	100
3	2021-01-03	300
4	2021-01-03	400
5	2021-01-03	500

Output should look like the following

So it should not include the ones that are added later with a "0" amount like client 5, but should null the amount of those that were in the table before like client 2

Client_Number	Date	Finance Amount
1	2021-01-01	100
2	2021-01-01	200
3	2021-01-01	300
4	2021-01-01	400
1	2021-01-02	100
2	2021-01-02	0
3	2021-01-02	300
4	2021-01-02	400
1	2021-01-03	100
2	2021-01-03	0
3	2021-01-03	300
4	2021-01-03	400
5	2021-01-03	500

Cheers

Julian

[mceleavey]

Hi @julianhoetzel ,

the logic here doesn't follow. Could you explain more about what you're trying to do here. There doesn't appear to be consistent logic.

M.

[mceleavey]

Hi @julianhoetzel ,

I think I understand now.

You want to include every Client Number up to the max number for that date. If they aren't there, then create them with a value of zero.

I've built it to do this as follows:

I hope this helps,

M.

[julianhoetzel]

Hi,

so a new client should appear in the dates but with a "0" when the client does not appear in the list the following day. 

A new client should not appear in the past. 

It's about consistency that every client shows up in the future even though the client does not show up in the actual list (First table)

Does that makes sense?

[mceleavey]

@julianhoetzel ,

try that above.

M.

[Maskell_Rascal]

@mceleavey 

I was thinking of something along the same lines as your example workflow, but got stuck when it comes to a practical application. I'm just gonna assume that the client numbers aren't perfectly sequential, so I'm not sure how to handle for that. Any thoughts?

[mceleavey]

Hi @Maskell_Rascal ,

Yeah, as above I used the sum tool to determine the max number for each date, then appended to create the valid combinations. This should work, but as you say, it is based on the Customer Numbers being sequential. However, you can get around this by using a RecordID tool.

M.

[julianhoetzel]

The client number is not the sum of clients it is rather a unique identifier like client number 2003432 or ABC123. Does that make more sense?

[mceleavey]

@julianhoetzel ,

I didn't create a sum. Can you confirm that worked?

M.

[mceleavey]

@julianhoetzel ,

I've now made it dynamic without using the max client number. This should work now.

M.