Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

denareavis
Contributor

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

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

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;

8 Replies

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

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;

denareavis
Contributor

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

It worked!!!

Thank you so much! You are AWESOME!

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

I am glad I was able to help

denareavis
Contributor

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

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

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

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

denareavis
Contributor

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

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

denareavis
Contributor

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

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

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

No problem at all

Community Browser