Stright Table Expression

[#1]

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

[awhitfield]

Hi John,

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

Andy

[#3]

HI Andrew Whitfield,

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

Regards

John

[Vegar]

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

[#5]

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

[PrashantSangle]

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

[Vegar]

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

[PradeepReddy]

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

[#9]

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

See the attachment then , based on your xls