Unable to get proper o/p with Only function

[Karim_Khan]

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,

KK

New to QlikView‌stalwar1‌celambarasan‌saimahasan‌

[sunny_talwar]

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?

[vinieme12]

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)

[Karim_Khan]

Will it be fine

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

[CELAMBARASAN]

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.

[Karim_Khan]

Hi Adhimulam,

    PFA

[vinieme12]

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,Service Request Closed

P1,CLOSED,C1,CLOSED,Service Request Closed

P1,CLOSED,C1,CLOSED,Service Request Closed

P1,CLOSED,C1,CLOSED,System Closed

P2,OPEN,C2,CLOSED,Service Request Cancelled

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 )

[CELAMBARASAN · Accepted solution]

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;

[Karim_Khan]

Hi Sir,

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

Regards,

KK