Derive column by copying row values

[JDong]

Hi All,

Please refer to the below sample data :

Country	Vehicle	Type	Engine	Sales	Sales Final
USA	Cars	Sedan	Engine1	500	500
USA	Bus	Double	Engine1	700	700
Germany	Cars7	Small	Engine2	67	500
Germany	Bus4	Third	Engine2	33	700
Korea	Bus2		Engine3	56	500
Korea			Engine3	72	700

I need to derive in a flow the last column. The last column is based on the USA data. We just need to fill in the USA data accordingly for Germany and Korea.

Sometimes the data in Vehicle and Type would be missing, but still the data  is to be derived in last column. Any idea on the logic to be used here.

Please note that my filters would work on the 'Engine' field

Thanks

[DavidP · Accepted solution]

Hi @JDong 

Would something like this work for you?

[ehughes]

Hi Gallop,

Are there always two rows for each country? If so, something like this could work:
(I've attached a workflow where you will be able to look at the formulas more closely)

[JDong]

Hi @DavidP 

This is very close to what I need...but only thing is the values are dropped after the join

In my actual case...I have 3 columns

Level 1  |   Level 2    | Level 3

Maybe Level 2 and Level 3 have number of blanks and hence the records dropping ?

But I see the values in the derived fields.

How to add back the remaining dropped records after the join ? would union work after join and I do a left join.

Thanks

[JDong]

Hi @ehughes 

Good question..there would be multiple rows for each country. But in all the record set would be limited to a fixed set of rows say 80 rows.

Thanks

[DavidP]

Hi @JDong 

Can you post an example - that would make it easier for me to understand

[JDong]

Hi @DavidP 

In the example you provided we notice one record dropped and showing in the left join

This seems to be the issue in my actual flow. Is there a way we can still show these records in the final output.

I tried a union after changing join condition to left but does not seems to pull the records in.

Thanks

[DavidP]

Ok, yes, I get it now.

Yes, you can use a Union tool, like this. The reason it didn't join is because the Vehicle field was hull and the Join tool uses that field to join. So all the rows like that will not have a Sales Final value. 

[ehughes]

My solution should work if the number of rows for each country remains constant.

[JDong]

Hi @DavidP 

That is exactly what I wish to resolve 🤔

Any way to show the data even if column has Null ?

As a standard the user just needs the derived column in a report to verify with the other values