Decoding status based on a measure and dimension

[jayviz]

I have this dataset I get after couple of other transformations. What I am trying to do is to display a status against each company based on 'Unit Sold' for a particular week num and year

e.g. Microsoft and Amazon did not sell anything for 2020 Week 2 - so status column should display 'Nothing Sold' against them by adding those 2 rows for 2020 Week 2 . Similarly for 2019 week 4 , Amazon did not sell anything - so it should add another row to display the 'Nothing sold' status for that.

Company	Week Num	Year	Unit Sold	Status
Google	1	2020	50
Microsoft	1	2020	100
Apple	1	2020	15
Amazon	1	2020	20
Google	2	2020	5
Apple	2	2020	10
Google	4	2019	80
Microsoft	4	2019	50
Apple	4	2019	60

[vizAlter]

Hi @jayviz — Welcome to the community!

It's good to attach sample/dummy data to know more about your Input file and desired output file, both?

[ConnorK]

Hi @jayviz ,

I have attached a documented solution that should help with this. The key steps are first checking to see if all weeks are listed regardless of company, and secondly using the cross-tab and transpose functions to build the required week rows for each company.

I hope this helps!

Best,

[jayviz]

@ConnorK - I tried what you sent. Slight issue there is If I add week '6' to 2019 and lets say 2020 only have weeks until week 4, it will add week 6 to 2020 regardless. That will cause issues.

so our input will become 

Output will be 

which obviously adding unwanted week 6 to 2020 as well.

[jayviz]

@vizAlter  Here is what I am trying to achieve

Input

Company	Week Num	Year	Unit Sold
Google	1	2020	50
Microsoft	1	2020	100
Apple	1	2020	15
Amazon	1	2020	20
Google	2	2020	5
Apple	2	2020	10
Google	4	2019	80
Microsoft	4	2019	50
Apple	4	2019	60

Desired Output

Company	Week Num	Year	Unit Sold	Status
Google	1	2020	50
Microsoft	1	2020	100
Apple	1	2020	15
Amazon	1	2020	20
Google	2	2020	5
Apple	2	2020	10
Microsoft	2	2020		Nothing Sold
Amazon	2	2020		Nothing Sold
Google	4	2019	80
Microsoft	4	2019	50
Apple	4	2019	60
Amazon	4	2019		Nothing Sold

[echuong1 · Accepted solution]

Try the attached:

You can avoid including additional weeks that didn't have data by creating combinations of the weeks and years with data via a summarize tool.

[jayviz]

@echong1 this is Perfect ! Thank you !!