Need to pick only "Not In Order" records

[nevilledhamsiri]

Hi

Dear friends!

Below data present my excel work sheet which data to be loaded in qlikview. My requirement is to pick only Not in order records. Not in order records stands for same ref_number for different name combination. Will you please propose an expression for this. Expect somebody to help me on this.

Regards

Neville

NAME    REF_NUMBER	AMOUNT
SANATH    111111	12000    IN ORDER
SANATH    111111	15000
PIYAL    222222	15000    IN ORDER
PIYAL    222222	25000
NIMAL    333333	2514   NOT IN ORDER
SARATH    333333	45000
PIYAL    444444	25000    NOT IN ORDER
DIMAL    444444	25000
KUSUM    777777	14500
KUSUM    777777	15000    IN ORDER
KUSUM    777777	154000
KAMAL    252525	14500   NOT IN ORDER
CIB_BROKER    252525	15500
CF_FINANCE    232323	16500
NALEEN    232323	17500   NOT IN ORDER
PRASAD    232323	25000
SIRIL    555555	16500
SIRIL    555555	17500    NOT IN ORDER
SIRL    555555	22000
PRASAD    555555	17500

[sunny_talwar]

Is this needed in the script or front end chart?

[nevilledhamsiri]

Yes  if you show me as an expression as well as how it is done in script that is absolutely appreciated!

Regards

Neville

[sunny_talwar]

NOT IN ORDER is within a field? or is this just to highlight what needs to show up? If it is not a field, then what is the logic to pick from the two NAMEs which have the same REF_NUMBER as it appears you want to just show one of the 2 or 3?

[nevilledhamsiri]

Dear sunny,

I just added comments such as "Not in order" &"Order"  to make the case clear.In fact there are only three fields such as Ref_number, name (Dimensions) & Amount (expression). What I need is when ever there are cases where same reference number assign to different names may be 2 ,3 or more to highlight them. Actually what I have noted as "Not in order" are the cases to be highlighted. Please propose an expression to pick them!

Thanks in advance!

Neville

[sunny_talwar]

You can do this like this

Dimension

NAME,

REF_NUMBER

AMOUNT

Expression

If(Count(DISTINCT TOTAL <REF_NUMBER> NAME) > 1, 1 0)

This will show all the rows where a single REF_NUMBER is associated with 2 or more NAMEs.

But you don't seem to want all of them, do you? you want one of the 2/many to show up, right? what is the logic? For instance

Why only see NIMAL and not SARATH and NIMAL?

[nevilledhamsiri]

Dear Sunny!

If I put it this way, highlight all cases where names are not the same but same reference number.ref number 333333 carries two different names such as nimal & sarath so that combination is not in order. When ever same name is not involved for single (same) ref_number, such cases are required to be highlighted. Is there a way to design a expression? that is what I need.

Thanks

Neville

[sunny_talwar]

Did you try this?

[nevilledhamsiri]

Yes I works! You need me to take cases which takes number 1 whereas cases take 0 to be disregarded.That is ok. Could you show me as to how I may excluded 0 cases  so that I can transfer to excel only cases which are not in order  & can further analyse by sending it to the users etc. Also If you dont mind can you show me the way I could do the conditional formatting to highlight them like we do in excel.

Thanks

Neville

[nevilledhamsiri]

Dear Sunny I may close the tread as "Correct" , because your expression did work!. Also please help me on my related quries

Thanks

Neville