topic Re: Applying logic to certain rows only in QlikView

Applying logic to certain rows only

Tue, 25 Aug 2015 13:27:09 GMT

Hi I have a problem where the sales figures are out by a factor of 10 for a certain sales person in a certain region.

So from my raw file I want to work out for rows where 'SALESPERSON' equals John and 'REGION' equals 'AMERICA' then divide the 'SALES' for these lines by 10.

Would I do this in script and if so how would this write out ? Note I am not changing all the 'SALES' figures but just those particular ones.

Re: Applying logic to certain rows only

sinanozdemir — Tue, 25 Aug 2015 13:30:40 GMT

Hi,

Without seeing the data, it is a bit difficult. Although, you can do something similar to the below:

LOAD

If(SALESPERSON = 'John' And REGION = 'AMERICA', SALES/10) As [Sales Factor]

Re: Applying logic to certain rows only

Tue, 25 Aug 2015 13:33:56 GMT

Thanks but I dont want to create a separate field. I think the logic would need to be applied to the original 'SALES' field if im not mistaken

Re: Applying logic to certain rows only

kaushiknsolanki — Tue, 25 Aug 2015 13:34:30 GMT

HI,

You can write the If statement in your script to do this. (Applicable only for few Salesperson).

Load *,if(Salesperson = 'John' and Region = 'America',Sales/10,Sales) as New_Sales

from XYZ;

If you have a big list with different factors then try below script.

Map:

Mapping Load Salesperson&'-'&Region as Key, Factor

From PQR;

Data:

Load *,Sales/if(isnull(Factor),0,Factor) as New_Sales;

Load *,Applymap('Map',Salesperson&'-'&Region) as Factor

From XYZ;

Regards,

Kaushik Solanki

Re: Applying logic to certain rows only

nagaiank — Tue, 25 Aug 2015 13:36:06 GMT

If name of your table having sales data (your field SALES) is SalesTable, you may use the following script. Use UpdatedSALES instead of SALES in your objects.

Sales:

Load *,If(SALESPERSON='John' and REGION='AMERICA', SALES/10,SALES) as UpdatedSALES;

Load * From ..... SalesTable;

Re: Applying logic to certain rows only

sinanozdemir — Tue, 25 Aug 2015 13:37:01 GMT

LOAD

If(SALESPERSON = 'John' And REGION = 'AMERICA', SALES/10, SALES) As SALES

In this case, you are not creating extra field and handling everything within SALES field.

Re: Applying logic to certain rows only

sasiparupudi1 — Tue, 25 Aug 2015 13:40:43 GMT

If(lower(SALESPERSON) = 'john' And upper(REGION) = 'AMERICA', SALES/10,SALES) As SALES

Re: Applying logic to certain rows only

Tue, 25 Aug 2015 14:31:32 GMT

Perfect - that's what I was looking for.