Hey Folks, I have a case where I need to go from table 0 to table 2... but I'm finding an issue that those with null values in table 2 are getting ignored. But I need them like that in the final output. Table 0EntityIDHead AccountA-B-CDE3478234987A6745234987B3987573892B5637421012B6789421014B7908321480B6789414127B5478987042A3498412345A4678421012B2987573892A Table 2ATLBTLHead Account3478674523498729873987573892467856374210124210146789421014321480790832148041412767894141275478 9870423498 412345 Book1.xlsx

Filter and Join and Filter

Hey Folks,

I have a case where I need to go from table 0 to table 2... but I'm finding an issue that those with null values in table 2 are getting ignored. But I need them like that in the final output.

Table 0
EntityID	Head Account	A-B-CDE
3478	234987	A
6745	234987	B
3987	573892	B
5637	421012	B
6789	421014	B
7908	321480	B
6789	414127	B
5478	987042	A
3498	412345	A
4678	421012	B
2987	573892	A

Table 2
ATL	BTL	Head Account
3478	6745	234987
2987	3987	573892
4678	5637	421012
421014	6789	421014
321480	7908	321480
414127	6789	414127
5478		987042
3498		412345

Book1.xlsx

Join

Preparation

Accepted answers

alexnajm

I think you are just looking for a Crosstab tool based on your final output then - Group by "Head Account", new column is "A-B-CDE", and values are Entity ID

However I don't think your data can get to the final output you have, so please check your original data first. For example, how is the 421014 row getting created when there are two B records?

Screenshot 2025-10-13 091815.png

alexnajm

I think you need to still be more clear on how you want this process to exist long term, but this gets you an end result that matches your required output

Filter and Join and Filter.yxmd

All comments

alexnajm

I'm confused - what is the final expected output? What is Table 1 in the Excel? Any more detail about the beginning and the end of your process would be greatly appreciated.

akvsachin

Hey @alexnajm

Table 0 is my input,
Table 2 is my expected output.

Table 1 is there because I was using a wrong method to do the same.

alexnajm

I think you are just looking for a Crosstab tool based on your final output then - Group by "Head Account", new column is "A-B-CDE", and values are Entity ID

However I don't think your data can get to the final output you have, so please check your original data first. For example, how is the 421014 row getting created when there are two B records?

Screenshot 2025-10-13 091815.png

akvsachin

Hey @alexnajm ,

Example like Record number 4 would not be ideal or expected value. So, how to get this sorted ?

Input.xlsx

alexnajm

@akvsachin my example uses the original data you attached - it is a text input so I could just use Table 0. This is the same data as previously posted.

Please try the workflow and come back with questions from there!

alexnajm

@akvsachin I see the edited comment - your data is not consistent to follow the assumed rules that you want to implement. For example, row 4 exists the way it does because both are labeled as "B" - why are both labeled as "B" when one should be labeled as "A"?

So either the data is off, or the process needs to be clarified

akvsachin

Hey @alexnajm ,

That was the exact solution.

Please help me with one more case where "when there are no records available with A-B-CDE=A then create a column [A] with null records.

alexnajm

I think you need to still be more clear on how you want this process to exist long term, but this gets you an end result that matches your required output

Filter and Join and Filter.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703