Max Date as A New Column

[suby]

All,

please find the sample flow and the expected output screen shot.

In the Expected Output I would to display the PRO Date column and a new  New Colum called With Max Date grouped by ID column.

[fmvizcaino]

Hi @suby ,

I'm attaching a solution to insert the max date in your dataset.

I'm not sure how to join the PCODE to your main dataset since you I'm not sure the exact rule to do it. 

Best,

Fernando Vizcaino

[suby]

Hello

Thanks for sharing this.

The table 1 and table 2 is joined by ID column in my original workflow and the rule is to Pick the Max PCode for Each ID and sort that the New Field TEST by Descending.

 You don't have to do anything you can use my existing flow but In the end I am looking for the expected output.

Much appreciate you response.

[fmvizcaino]

Hi @suby ,

But I still didn't understand how to join by ID table 1 and 2.

For example, ID 72 has PCODE IC and IR and in your output you have only IR

But for ID 73, you have all 3 incidences from table 2. I need one more rule to be able to join the PCODE to the table 1, for instance, the date for each line of ID + PCODE.

Best,

Fernando Vizcaino

[suby]

Hi,

Apologize it was a Mistake on the Output screen shot.

I've attached 4 attachments now.

Workflow1 

Workflow 1 - Output

and

Workflow2

Revised Output

Workflow1 & Workflow 1 - Output - was my initial requirement and  we managed to get that working see the attached.

Workflow2-

In this workflow I've amended the Table 1 to Include the Dates for you to help and I've also included the Revised output screenshot and this is what the final output should look like.

Also one more thing to mention here is that in your earlier solution you used Date time tool to convert that from string to Date which is fair enough but in my actual data set its already a Date Data type and is there a way to get this working using the Append Field tool.

Hope this is clear now. Apologize again.

[fmvizcaino]

Hi @suby ,

I'm still not sure how to join the specific PRO Date with the PCODE, is there a specific rule?

From what I'm seeing, if we look to ID=72 the PCODE has an order and if we look to ID=73, the PCODE has a different order.

if you want to join based on the record position, it is possible to do it with the option below from join tool.

[suby]

Hi,

Apologize If I  missed anything.

Please see the attached the workflow In this I've  put the summarize tool after the JOIN TOOL and  did a MAX on Test column and a MAX on PRO Date and grouped by ID and I am  getting the expected results in terms of PCODE and PRO Date

In the attached workflow I get the MAX PRO Date for all the IDs which is fine but now what I am looking for now in he Output is

- Bring the Actual PRO Date column as well in the Output will satisfy my requirement.

or

Suggest me a way to bring the Actual PRO DATE for all the rows and create a new column call M Date which gets the MAX Date grouped by ID.

[fmvizcaino · Accepted solution]

Hi @suby ,

I've changed your workflow a bit but you are getting the desired result now.

Let me know if you need further help.

Best,

Fernando Vizcaino

[suby]

Hello,

Thanks for your time and the solution works and can I ask you something can you amend the workflow to create a New column Called MDATE using the Append fields Tool and expecting the same Out put Result.

You've been great Thank You s Much

Thanks