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: 
cmorri1988
Creator
Creator

Complex Set Analysis - Aggregation and P()

Hi there,

I am based on the following selections:

selections.PNG

 

using this measure:

only({1<ID = 
p({<CT_KEY = (P(CT_KEY)),
bisac_sbj1_code = (p(bisac_sbj1_code)),
bisac_sbj2_code = (p(bisac_sbj2_code)),
bisac_sbj3_code = (p(bisac_sbj3_code)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}

//Aggregate each match over each selected ID and CTKEY
Aggr(

only({1<ID = 
p({<CT_KEY = (P(CT_KEY)),
bisac_sbj1_code = (p(bisac_sbj1_code)),
bisac_sbj2_code = (p(bisac_sbj2_code)),
bisac_sbj3_code = (p(bisac_sbj3_code)),
[Price] = {">=$(vMinCTPrice)<=$(vMaxCTPrice)"},
[Pub Date Num] = {">=$(vMinCTPubDate)<=$(vMaxCTPubDate)"},
ID = e(ID),Author = , Title =, ISBN = >})>}
Rank6)


,CT_KEY, ID))

the problem I am having is the results are using possible bisac_sbj2_code values for any selection, and not those related to each ID and CTKEY.

Results.PNG

 

From the results, bisac_sbj2_code is blank, but from the selection ISBN 9780007382767,  bisac_sbj2_code is OCCO19000, and so I would like these results to have this code, being aggregated for each selected CTKEY and ID.

 

Any help most welcome!

 

Thanks!

 

 

 

Labels (3)
11 Replies
sunny_talwar

As you have mentioned in your post's heading, this is a fairly complex issue. Would it be possible for you to share a sample to check this out?
cmorri1988
Creator
Creator
Author

Hi,

 

I am receiving an error when trying to attach the QVF, I can send you an invite from Qlik Cloud if you have an email address I could share the app with?

 

Thanks

sunny_talwar

Can you check if you can upload the file by zipping it? The problem is that I don't know if I can open your cloud from my work place.

cmorri1988
Creator
Creator
Author

Thanks,


I cant post the QVF without error or indeed with it zipped, but have attached sample data in the form of an xlsx.

Below are the two tables required to test, and their 5 dimensions.

dims and measures.PNG

 
For the bottom table, please add this measure:
 
only({1<ID = 
p({<CT_KEY = (P(CT_KEY)),
[BISAC Subject Code] = (p([BISAC Subject Code])),
bisac_sbj2_code = (p(bisac_sbj2_code)),
ID = e(ID), Title =, ISBN = >})>}

//Aggregate each match over each selected ID and CTKEY
Aggr(

only({1<ID = 
p({<CT_KEY = (P(CT_KEY)),
[BISAC Subject Code] = (p([BISAC Subject Code])),
bisac_sbj2_code = (p(bisac_sbj2_code)),
ID = e(ID), Title =, ISBN = >})>}
ID)


,CT_KEY, ID))

 Please select ISBN's 9780060834166 and 9780007382767 to test.

 You will see results with a CT_KEY of 1562 have "bisac_sbj2_code" of NULL when this should be OCC019000 based on your selections and the measure.
 
Thanks
 
Thanks
sunny_talwar

Checking it now
sunny_talwar

I followed your directions and this is what I am getting. What is the problem here?

image.png

cmorri1988
Creator
Creator
Author

Thanks,

If you look at the results, I only expect results with CT_KEY 1562 to have a bisac_sbj2_code of OCC019000, however, results with CT_KEY 213 also have OCC019000, when based on the selections CT-
_KEY and bisac_sbj2_code combination, this should be NULL

Thanks
sunny_talwar

Is this what you want?

image.pngInstead of using

bisac_sbj2_code

as a dimension.... use this as a measure

If(Len(Trim(Column(2))) > 0, Only(TOTAL <CT_KEY> bisac_sbj2_code))
cmorri1988
Creator
Creator
Author

Hi there,

 

Unfortunately I require this to be controlled within the measure.

The measure is a subset of a much more complex measure, which in the end produces a ranking, and therefore I need all logic to be performed within it.

I'm sure there is a way to aggregate over the bisac subjects as well as the ID and CTKEY but cannot find it.

 

Thanks