topic Re: wildmatch and sum in QlikView

wildmatch and sum

raadwiptec — Mon, 15 Feb 2016 10:24:54 GMT

Hi ,

I have a pivot table..

first I need to consolidate all the wild match name to one name

for ex: column name is 'Postion' - data is as below.

moved, transit, moved out,moved in,outin moved, transitout,transitin..etc

so I need to have all the moved in wildmatch and have common name as 'moved'

secondly I have cost for each position for

moved

cost is 100 pounds

moved out

cosit is 150 pounds

etc.

so when making the postion as moved ..I also need the sum of all the cost ..it should be like this

Moved(It includes as moved)

250 (It is sum of cost for all moved)

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 10:31:27 GMT

sum(moved)/sum(total moved)

sum(moved)/sum(All moved)

Re: wildmatch and sum

PrashantSangle — Mon, 15 Feb 2016 10:35:23 GMT

Hi,

What is your table structure for maintain the cost???

Is it maintain in same table or different table??

For your 1st query try to create new field in script like

if(wildmatch(postion,'*moved*'),'Moved',postion) as newPostion

then use newly created field as dimension and sum(Cost) as expression.

Regards

Re: wildmatch and sum

raadwiptec — Mon, 15 Feb 2016 10:36:51 GMT

First I need to name all the wild matched names to a single name?

for ex: if wildmatch(Position ,'*moved*') ,Postions

here I get all the values in different column.. I need in one column

Re: wildmatch and sum

jonathandienst — Mon, 15 Feb 2016 10:37:40 GMT

Could you please supply some more representative data. What dimension/grouping do you need for this analysis?

Re: wildmatch and sum

Anonymous — Mon, 15 Feb 2016 10:39:07 GMT

Different Column in Different Tables??

Different Column in same table??

Could you share sample data?

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 10:42:21 GMT

If(WildMatch([Position ],'*moved*'), 'moved', 'movedout')

Re: wildmatch and sum

PrashantSangle — Mon, 15 Feb 2016 10:43:50 GMT

Hi,

did you try my suggestion???

Try below.

if(wildmatch(postion,'*moved*'),'Moved',postion)

Regards

Re: wildmatch and sum

raadwiptec — Mon, 15 Feb 2016 10:46:09 GMT

It is in the same table. I want to consolidate all moved using wildmatch into one single name ' Moved' similarly 'Transit

Add all the cost for moved .I am trying this in an expression

so it should be as below. even country in same table

Its a pivot table

Moved ¦ transit

UK 700 300

Position	Cost
Moved	100
MovedOut	150
MovedIn	200
OutMoved	250
InTransit	100
Outtransit	200

Re: wildmatch and sum

PrashantSangle — Mon, 15 Feb 2016 10:48:07 GMT

Hi,

Can you post sample data and your script to load that file...

Regards

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 10:51:26 GMT

if(Wildmatch(postion,'*moved*transit*'),postion)

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 10:56:16 GMT

or try this,

Pick(WildMatch(Position, 'Moved*','InTransit*'), '100', '100')

lik all

Re: wildmatch and sum

raadwiptec — Mon, 15 Feb 2016 11:02:12 GMT

Hi suresh this gives all the moved in different columns..

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 11:06:38 GMT

, sum(if(WildMatch(Position,'*moved*'),'moved','transit') as newposition;

Re: wildmatch and sum

sujeetsingh — Mon, 15 Feb 2016 11:08:02 GMT

Better you group the wildmatch group in script.

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 11:09:05 GMT

hope helps this

if(wildmatch(Position, 'Moved*'),Sum(cost)) as newpostion

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 11:12:01 GMT

Is Sum(cost) and

(if(wildmatch(position, 'Moved*'))) as newpos

are separate fields?

If they are separate fields then use

Sum(cost) AS Totalcost,

(if(wildmatch(position, 'Moved*'))) as newpos

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 11:18:00 GMT

sum({<[position]={'moved','transit'}>}cost)

Re: wildmatch and sum

Chanty4u — Mon, 15 Feb 2016 11:20:26 GMT

Sum( If( wildmatch( position, 'moved*', ) and position= 'transit' cost))

Re: wildmatch and sum

sasikanth — Mon, 15 Feb 2016 11:50:46 GMT

HI,

try this script

LOAD *, if(WildMatch(Position,'*Moved*'),'Moved',if(WildMatch(Position,'*Transit*'),'Transit',Position)) as NewPosition;

LOAD * INLINE [

Position ,Cost

Moved ,100

MovedOut, 150

MovedIn ,200

OutMoved, 250

InTransit, 100

Outtransit ,200

];

You can use NewPosition as your desired column