WILMATCH AND PICK

[#1]

I have two fields containing these values :

Header 1	Header 2
"JU | PLAN"	1
"JU | ACTUAL"	2
"FBP | PLAN"	3
" FBP | ACTUAL"	4
"LRFP | PLAN"	5
" NU | ACTUAL"	6

I want to aggregate(sum) JU together and FBP together and  "ACTUAL" together

the result should come like this : JU - 3 , FBP - 7 , ACTUAL - 12

PLEASE check the below code :

IF(wildmatch(SNAP_Type_LE_BREAKUP,('JU |*') ,('FBP |*'),'*ACTUAL'),

PICK(wildmatch(SNAP_Type_LE_BREAKUP,'JU |*','FBP*','*ACTUAL')

,'JU','FBP','ACTUAL'))

this code is giving correct result for JU and FBP but FOR ACTUAL IT IS CONSIDERING  ONLY NU | ACTUAL FOR SUM"

[sunny_talwar]

Not sure I completely understand what you are trying to do, can you may be share your sample qvw file?

[rupamjyotidas]

Since it conditional statement , what ever is left out is coming under 'Actual', so only NU.

If I understand you are looking for something like

Sum is equal to

[maxgro]

W:

LOAD [Header 1],

     [Header 2]

FROM

[https://community.qlik.com/thread/234923]

(html, codepage is 1252, embedded labels, table is @1);

W2:

LOAD [Header 1], 

  Pick(wildmatch([Header 1], '*JU |*', '*FBP |*'), 'JU', 'FBP') as link

Resident W;

LOAD [Header 1], 

  if(wildmatch([Header 1], '*| ACTUAL*'), 'ACTUAL') as link

Resident W;

[Anonymous]

You can also use subfield function to achieve the same:

Raw_Data:

LOAD * INLINE [
Header1, Header2
'JU|PLAN', 1
'JU|ACTUAL', 2
'FBP|PLAN', 3
'FBP|ACTUAL', 4
'LRFP|PLAN', 5
'NU|ACTUAL', 6
];
NoConcatenate
load Header1,sum (Header2) as total
group by Header1;
Load SubField(Header1,'|') as Header1,Header2
resident Raw_Data;
  drop table Raw_Data;