Sorting by with two levels

[#1]

Hi,

In the Load script on Qlikview, I'm wondering how I can sort by two criteria, so it'd sort by a number first but if the number is the same, it'd order by age. I've been trying to use ORDER BY Main_MEM_NO_TEMP,RENEWAL_AGE; (those are the two variables I want to sort by), but that doesn't seem to be working. Anyone have any ideas

Code I have is

MemPolicy_Temp:

LOAD

  JOIN_MEM_NO,

  JOIN_MEM_NO as JOIN_MEM_NO_TEMP,

  Main_MEM_NO as Main_MEM_NO_TEMP,

  RENEWAL_AGE

RESIDENT MemPolicy

where mixmatch(ASC_RENEWAL,'Child',ASC_RENEWAL = 'Student')

order by Main_MEM_NO, RENEWAL_AGE

;

MemPolicy_Child:

LOAD

  JOIN_MEM_NO,

  If(Main_MEM_NO_TEMP <> Previous(Main_MEM_NO_TEMP), 1,

  NumSum(peek(Counter) + 1)) as Counter

RESIDENT MemPolicy_Temp

ORDER BY Main_MEM_NO_TEMP,RENEWAL_AGE;

So, it should have a counter of 1 for the oldest child, and a counter of 2 for the next oldest. However, it's placing the counter on the one with the first membership number

[sunny_talwar]

So the script isn't adding counters to all the children? It is just adding 1 for the oldest child? Is that what the issue is?

Sunny

[john_s_w_wood]

do you have an example of your data please?

[#4]

Nope, it adds counters to all the children, but it adds it based on highest member number instead of oldest child. So, the if a member ends in 2 and is a child, it'll get assigned 1 over a child that ends in 3 even if the one that ends in 3 is the older child.

[sunny_talwar]

I am not completely sure if I understand your data, but to check if the order by is working or not, why don't you add RowNo() as S.No. to your MemPolicy_Child table and see what order it is sorting everything. This might help you to figure out what you need to do in order to fix the ordering. Else please share a sample.

Best,

Sunny

[#6]

Main Member	Member	Age	Allocated Child Counter	Correct Counter
4006720100	4006720105	16	2	1
4006720100	4006720103	15	1	2

That would be an example of data. I'd like it to first sort by main member and then sort by age, but it seems to be sorting by main member and then member instead.

[sunny_talwar · Accepted solution]

You need to sort the age in Descending order.

Try this:

MemPolicy_Child:

LOAD

  JOIN_MEM_NO,

  If(Main_MEM_NO_TEMP <> Previous(Main_MEM_NO_TEMP), 1,

  NumSum(peek(Counter) + 1)) as Counter

RESIDENT MemPolicy_Temp

ORDER BY Main_MEM_NO_TEMP,RENEWAL_AGE desc;

Assuming RENEWAL_AGE is the Age in your below table.

HTH

Best,

Sunny

[#8]

Thank you. Feel like and idiot that it was that small, but thank you.

[sunny_talwar]

No problem. We all make these small errors. But I am glad your were able to fix it.

Best,

Sunny