topic Re: Unable to get proper o/p with Only function in QlikView

Unable to get proper o/p with Only function

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I wanted to get the Paent Status on the basis of Child Status.Suppose I have 5 child and their status is as below

c1-Open

c2-Closed

c3-Closed

c4-Open

c5-Open

If any one of the Child above is Open then Parent status should consider as Open and other logic is as below

c1-Closed

c2-Closed

c3-Closed

If all of the Child above is Closed then Parent status should consider as Closed .I am using the below code in script but the o/p is coming wrong

LOAD:

IF(ONLY(CHILD_STATUS)='CLOSED','CLOSED','OPEN') AS PARENT_STATUS

TABLE X;

Regards,

New to QlikView‌stalwar1‌celambarasan‌saimahasan‌

Re: Unable to get proper o/p with Only function

sunny_talwar — Wed, 21 Dec 2016 13:16:26 GMT

In the Excel file attached you don't have c1, c2, c3... what are these? Only thing I see is PARENT_STATUS and CHILD_STATUS?

Re: Unable to get proper o/p with Only function

vinieme12 — Wed, 21 Dec 2016 13:25:01 GMT

Assign numeric values to Status field during load

example

Dual(ChildStatus,if(ChildStatus = 'Open',1,2)) as ChildStatus

Now for your Parent status use expression = MinString(ChildStatus)

Re: Unable to get proper o/p with Only function

Karim_Khan — Wed, 21 Dec 2016 13:42:24 GMT

Will it be fine

PICK(WildMatch(ONLY(CHILD_STATUS),'CLOSED','OPEN'),'CLOSED','OPEN') AS PARENT_STATUS

Re: Unable to get proper o/p with Only function

CELAMBARASAN — Wed, 21 Dec 2016 14:13:12 GMT

Karim,

Share some sample data.

Just want to make sure how your data is and also this can't be done single load statement for both Child status and Parent status.

Re: Unable to get proper o/p with Only function

Karim_Khan — Wed, 21 Dec 2016 14:41:21 GMT

Hi Adhimulam,

PFA

Re: Unable to get proper o/p with Only function

vinieme12 — Wed, 21 Dec 2016 14:45:31 GMT

See attached app

LOAD *,dual(CHILD_SR_STATUS,if(CHILD_SR_STATUS='OPEN',1,2)) as DUAL_CHILD_SR_STATUS INLINE [

PARENT_ID,PARENT_STATUS,CHILD_ID,CHILD_SR_STATUS,CHILD_EXT_STATUS

P1,CLOSED,C1,CLOSED,Service Request Closed

P1,CLOSED,C1,CLOSED,System Closed

P2,OPEN,C2,CLOSED,Service Request Cancelled

P3,CLOSED,C3,CLOSED,Service Request Rejected

P3,CLOSED,C3,CLOSED,Service Request Closed

P3,OPEN,C3,OPEN,Pending Payout Approval

];

Dimension: =PARENT_ID

Expression= minstring(DUAL_CHILD_SR_STATUS )

Re: Unable to get proper o/p with Only function

CELAMBARASAN — Thu, 22 Dec 2016 11:56:50 GMT

Hi Karim,

You can use the below code

Parent:

LOAD PARENT_NUM,

CHILD_NUM,

CHILD_COUNT,

CURRENT_PARENT_STATUS,

CHILD_SR_STATUS

FROM

CHILD_PARENT.xls

(biff, embedded labels, table is [Sheet1$]);

Left Join(Parent)

LOAD PARENT_NUM,

If(CONCAT(DISTINCT CHILD_SR_STATUS) = 'CLOSED', 'CLOSED', 'OPEN') AS PARENT_STATUS

Resident Parent

Group By PARENT_NUM;

Re: Unable to get proper o/p with Only function

Karim_Khan — Thu, 22 Dec 2016 15:25:09 GMT

Hi Sir,

Thanks for your reply same has been rectified logic is same as implement by you.

Regards,