Designer| Exercise trying to solve.

[sravanbyreddy]

Hi guys,

I have struck with the problem where the data is 

Input:
1: First input contains a list of transactions which include the [Client_Number], [Month], & [Finance Amount]. Customers only appear on months that they have made a purchase. 
2: Second input contains [Client_Numers] and [Client_Names]. 

Output:
1: include current customers in the data set with:
2: add "finance amount" of 0 for the missing months  
3: include Client Names 
4: present them a table with the different months as column header.

here I am trying this a long time but i couldn't figure that out, hope you guys help me this problem. I am facing difficulty as I am newly learning about this Designer/Tool.

Hope you guys help me out,

Thanks a lot in advance. and appreciate any kind of suggestion or input for learning.

Thanks & Regards,

Sravan

[DavidP]

Hi @sravanbyreddy 

Is this an exam question of some sort? If so, you shouldn't post the question to the Community.

You should look at the CrossTab, Join and Data Cleanse tools to solve this problem.

[sravanbyreddy]

Hi David,

No, it's not an exam question. it was given as a practice exercise to solve, i have been trying since long but i couldn't make this possible so i am looking for a help to solve this.

Please help me out David. It really helps me learning regarding this type of problems, if i face any in future.

[Qiu · Accepted solution]

@sravanbyreddy 
As @DavidP commented, we are not allowed to answer questions from the certificat exam. and moreever, we are also not encouraged to give just the solution.
But since this problem has been trouble for you sometime, let me give a try.

1. We need to a cleansing first for 2nd input, since it contains some null rows and white space

2. We wil lthen join the the first input and seond input the key of "Client Number"

3. Next we will do a CrossTab to bring the Month as a header and the Order as Values

4. Something interesting is that the Client Name has to be removed for "_" and "-"

5. Last we use a Multi-Field Formula to convert those null to 0.

[sravanbyreddy]

Hi Qiu,

Thanks a lot for providing me a solution for this use case, I really appreciate the in detail explanation which was given by you. I here by say that this Question was not asked in any of the certification exam or any other exam. As i am learning this Tool i was practicing some of the exercises to get hands on on this tool, and came through this one.

Thanks a lot for your valuable time spent on this problem.

Thanks & Regards,

SravanByreddy

[Qiu]

@sravanbyreddy 
You are welcome and I am happy to be helpful. 😁
We are always happy to help each other in learning and using Alteryx.

Just drop by if you have other queries. 

[sravanbyreddy]

Mr. Qiu,

Thank you, Thanks a lot for your kind words, it means a lot. will surely drop a message to you if i have any queries regarding Alteryx in future.

[DavidP]

Hi @sravanbyreddy 

if you change the sequence of tools around slightly, you can achieve the desired output with only the 3 tools I mentioned. It's worth playing around with tools like the Crosstab and Data Cleanse too see everything they can do.

[Qiu]

@DavidP 
Nice one.
Thank you for the improvement! 😁

[mgetaneh]

An attempt with extra steps, but I need to optimize it with shorter steps. Still learning.