Filter and Join and Filter

[akvsachin]

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

[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 · Accepted solution]

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? 

[akvsachin]

Hey @alexnajm ,

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

[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 · Accepted solution]

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