Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a difficult hierarchy logic I do not know how to handle in a load script.
Here is the example, I have a list of "Interest Mappings" beginning data pictured on left, records 1-10. I need to select the Code, Department, Product, and Interest but I need to let a 'Y' value trump all if there is a Y value. If all values for a Product type are N, then the Interest needs to be N. The end result should be as pictured on the right, records 1-6, where description is not included. Basically this is to loosen up over-restrictive Interest Mapping by saying, "well, you were interested in other Apple pieces in your Compost product, but you were being to picky."
The beginning two records have only one interest value for each product so those are kept. The next three values are the same code, same department, same Product, except there is one Y and N in the other two. I need the Y to be selected over any N. That would result in the record #3 in the Ending data example.
I would picture this as a regular Group By, to group past the Code Description, except for the condition that Y value should override all other values within the Product type.
I have absolutely no idea how to do this. Please help if you can!!
Thank you very much!!!!
May be this:
Table:
LOAD Record,
Code,
[Code Description],
Department,
Product,
Interest
FROM
Community_206378.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD RowNo() as Record,
Code,
Department,
Only(Product) as Product,
If(SubStringCount(Concat(DISTINCT Interest, '|'), 'Y') = 1, 'Y', 'N') as Interest
Resident Table
Group By Code, Department, Product;
May be this:
Table:
LOAD Record,
Code,
[Code Description],
Department,
Product,
Interest
FROM
Community_206378.xlsx
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD RowNo() as Record,
Code,
Department,
Only(Product) as Product,
If(SubStringCount(Concat(DISTINCT Interest, '|'), 'Y') = 1, 'Y', 'N') as Interest
Resident Table
Group By Code, Department, Product;
It worked!!!
Thank you so much! You are AWESOME!
I am glad I was able to help
Could I just clarify, it won't matter if there are more than one Y. In that case, should I have >=1 or does the =1 refer to the value Y being there at all, once or 5 times? I just don't want to exclude any that might have two Y values. I can hardly find anything on the use of an Only function in a load script.
Thanks again!!
The reason I do = 1 is because I have DISTINCT when I Concat Interest. Making it greater than equal to 1 would work the same way. Added some additional rows to your data set:
Record | Code | Code Description | Department | Product | Interest |
1 | 123 | Apples | A | Gifts | Y |
2 | 123 | Apple Cores | A | Décor | N |
3 | 123 | Apple Stems | A | Compost | Y |
4 | 123 | Apple Peels | A | Compost | N |
5 | 123 | Apple Pulp | A | Compost | N |
6 | 456 | Oranges | A | Gifts | Y |
7 | 456 | Orange Peels | A | Décor | N |
8 | 456 | Orange Pulp | A | Compost | N |
9 | 456 | Orange Juice | A | Compost | N |
10 | 456 | Oranges Small | A | Compost | N |
11 | 789 | Bananas | A | Gifts | Y |
12 | 789 | Banana Cores | A | Décor | N |
13 | 789 | Banana Stems | A | Compost | Y |
14 | 789 | Banana Peels | A | Compost | Y |
15 | 789 | Banana Pulp | A | Compost | N |
The output I get is this:
Thank you for the explanation. I appreciate it so much!
Can you help me with this one? Re: Cross sale pivot
No problem at all