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: 
Anonymous
Not applicable

pick function + concatinate to field object

Hi,

Is there a way to make this a one liner instead of rerwite this 12 times?

 

pick

([COST Claim Repair Age Year]
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year1] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year2] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year3] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year4] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year5] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year6] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year7] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year8] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year9] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year10] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year11] ), [COST Claim Repair Age Year])
,
aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >} [CONTRACT Main Contract In Year12] ), [COST Claim Repair Age Year]

)
)

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

If I understand correctly what you want, it is:

aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >}

pick([COST Claim Repair Age Year], [CONTRACT Main Contract In Year1] ,[CONTRACT Main Contract In Year2] , ...[CONTRACT Main Contract In Year12] )

), [COST Claim Repair Age Year])

(Count my paretnthesis please)

Regards,

Michael

View solution in original post

3 Replies
Anonymous
Not applicable
Author

If I understand correctly what you want, it is:

aggr(NODISTINCT sum( {$<[COST Main Operation No1]={"*"}, [COST Main Operation No2]={"*"}, [COST Main Operation No3]={"*"}, [COST Main Operation No]={"*"} >}

pick([COST Claim Repair Age Year], [CONTRACT Main Contract In Year1] ,[CONTRACT Main Contract In Year2] , ...[CONTRACT Main Contract In Year12] )

), [COST Claim Repair Age Year])

(Count my paretnthesis please)

Regards,

Michael

Anonymous
Not applicable
Author

Yes, something like that. But when I change it this way the result is not the same anymore.

Anonymous
Not applicable
Author

Can't see why.  If you upload a sample application I (or someone else) will take a look.