topic Re: Stright Table Expression in QlikView

Stright Table Expression

Thu, 11 Jun 2015 12:16:20 GMT

Hi All,

can any one guide me on the following scenario, I have 3 fields Sales Agent Id, Location, Sales , by using these 3 fields we know

sales information per particular area for each sales agent.

in the Qlikview if I select any location ,it will show the data about that location, by discarding the null values or the sales agents who are not having the data. my requirement is I need to show those excluded customers also with default value 255. how can I achieve it.

Thanks

John

Re: Stright Table Expression

awhitfield — Thu, 11 Jun 2015 15:00:06 GMT

Hi John,

your xlsx appears to be empty, so is difficult to help!

Andy

Re: Stright Table Expression

Thu, 11 Jun 2015 16:31:24 GMT

HI Andrew Whitfield,

thanks for your inputs, I have updated new xlsx PFA.

Regards

John

Re: Stright Table Expression

Vegar — Thu, 11 Jun 2015 16:58:49 GMT

You could make it with an IF() in your expression.

if(sum(Sales),sum(Sales),(255))

Do not Supress zeros.

Show all dimensions.

Please take a look at my attached example.

Cheers

Vegar Lie Arntsen

Re: Stright Table Expression

Fri, 12 Jun 2015 04:46:40 GMT

Hi Vegar Lie Arnten

Thanks. in attachment I am getting all the Sales agents Ids but what I need, if I select NY would like to see only the Sales agents who are representing the NY. output like

1	NY	250
4	NY	230
5	NY	255
9	NY	255

Re: Stright Table Expression

PrashantSangle — Fri, 12 Jun 2015 05:02:16 GMT

Hi,

Use below script

Test:

LOAD [Sales Agent ID],

Location,

if(isnull(Sales) or len(trim(Sales))=0,255,Sales) as Sales

FROM

community1.xlsx

(ooxml, embedded labels, table is Sheet1);

and then in front end

take Straight table ->

Dimension : Sales Agent Id and Location

and

Expression :Sum(Sales)

Regards

Re: Stright Table Expression

Vegar — Fri, 12 Jun 2015 06:21:18 GMT

You need to remove show all values from Sales agent ID and add Location with the preferences in the pic. See also my qvw attachment

Re: Stright Table Expression

PradeepReddy — Fri, 12 Jun 2015 07:18:33 GMT

try it in back end... try with anyone option..

1) if(isnull(Sales),255,Sales) as Sales

2) if(isnull(Sales) or len(trim(Sales))=0,255,Sales) as Sales

Re: Stright Table Expression

Fri, 12 Jun 2015 12:17:42 GMT

I suppose you don't want to deal with null on Load script.

See the attachment then , based on your xls