Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dena_reavis
Employee
Employee

Help! Rollup or Group by: One Yes trumps any number of No values?

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!!

Group by.PNG

Thank you very much!!!!

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

8 Replies
sunny_talwar

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;

dena_reavis
Employee
Employee
Author

It worked!!!

Thank you so much! You are AWESOME!

sunny_talwar

I am glad I was able to help

dena_reavis
Employee
Employee
Author

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!!

sunny_talwar

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:

RecordCodeCode DescriptionDepartmentProductInterest
1123ApplesAGiftsY
2123Apple CoresADécorN
3123Apple StemsACompostY
4123Apple PeelsACompostN
5123Apple PulpACompostN
6456OrangesAGiftsY
7456Orange PeelsADécorN
8456Orange PulpACompostN
9456Orange JuiceACompostN
10456Oranges SmallACompostN
11789BananasAGiftsY
12789Banana CoresADécorN
13789Banana StemsACompostY
14789Banana PeelsACompostY
15789Banana PulpACompostN

The output I get is this:

Capture.PNG

dena_reavis
Employee
Employee
Author

Thank you for the explanation. I appreciate it so much!

dena_reavis
Employee
Employee
Author

Can you help me with this one? Re: Cross sale pivot

sunny_talwar

No problem at all