Fields giving 0 result

[#1]

Hi guys,

I run an SQl query to get the required fields.

now when I add all the fields and designs that I need of them is making a mess.

it is called division.

On my fields I have Division, Department, Names and revenue.

everything runing perfect with out having the division field selected.

once I select one of the divison options the revenue shows as 0.

also, when selected it is recognizing the right departments but keeping them white and turning the other departments which are not related to that division in gray color.

Any Idea on why the revenue turning into Zeros.

Thanks

[swuehl]

Not yet..

Are you looking at total revenue or revenue grouped by Names, Department and / or Division?

What happens if you select on one of the Department values (that "kept white") instead?

Are all Departments linked to at least one Division?

Could you post your data model here (maybe as screenshot)?

[#3]

By deleting the division field and choose any of the department or all the departments related to x division everything run perfect.

If I keep the division and select one of its value and then choose one of its related department the revenue is 0.

I am looking at department revenue.

[swuehl]

How does your expression to calculate revenue look like? Are you using a set expression?

[#5]

Can you post your load script or a copy of your application?

[#6]

The department and the divisions are loaded from an external excel spreadsheet.

belwo is the query

Thxs

Department_Map:

Mapping LOAD A,

     B

FROM

C:\QlikviewReports\Sourcedocuments\ApplyMapFiles\DepartmentsFY12PNL.xlsx

(ooxml, no labels, table is Sheet1);

Division_Map:

Mapping LOAD A,

     B

FROM

C:\QlikviewReports\Sourcedocuments\ApplyMapFiles\DivisionsFY12.xlsx

(ooxml, no labels, table is Sheet1);

BusinessUnit_Map:

Mapping LOAD A,

     B

FROM

C:\QlikviewReports\Sourcedocuments\ApplyMapFiles\BSUnits.xlsx

(ooxml, no labels, table is Sheet1);

PNL:

LOAD *,

    applymap('BusinessUnit_Map', BUSUNIT, 'Wholesale') as Department;   

select

OEF.LT as LedgerType,

OEF.CUCC# as CategoryNum,

OEF.Company,

OEF.GLDTE as GL_Date,

SUBSTR(DIGITS(OEF.GLDTE), 5, 2)||'/' || SUBSTR(DIGITS(OEF.GLDTE), 7, 2)||'/'||SUBSTR(DIGITS(OEF.GLDTE), 1, 4) AS ORDERDATE,

Year(OEF.GLDTE) as Year,

Month(OEF.GLDTE) as Month,

OEF.BUCC# as BusUnitNum,

OEF.BUCC as BUSUNIT,

OEF.ACCC# as AcctCatNum,

OEF.ACCC as AcctCat,

OEF.Amt as Amount,//Revenue

F1.DRDL01 as Description,

F1.DRDL02 as BusUnitName,

CASE

WHEN Month(OEF.GLDTE)>6 THEN (Year(OEF.GLDTE)+1)

WHEN Month(OEF.GLDTE)<=6 THEN Year(OEF.GLDTE)

END AS FY

from sddta.oef902  OEF

inner join common.f0005 F1

on OEF.ACCC = TRIM(F1.DRKY)and TRIM(F1.DRRT) = '08' and TRIM(F1.DRSY) = '09'

where

OEF.cucc# = 13

and YEAR(OEF.GLDTE) > $(#varPreviousYear)

and OEF.company = 'ATL'

and OEF.bucc# = 9

and OEF.accc# = 8

and OEF.accc < 820

and DATE(SUBSTR(DIGITS(OEF.GLDTE), 5, 2)||'/' || SUBSTR(DIGITS(OEF.GLDTE), 7, 2)||'/'||SUBSTR(DIGITS(OEF.GLDTE), 1, 4)) <= current_date

;

[swuehl]

badrharfoush wrote:

The department and the divisions are loaded from an external excel spreadsheet.

But I can't really see how you load and link these two fields, I think that might be quite important to your problem.

I only see a map from BusinessUnit_Map to a field called Departement, but what are you doing with the other two mapping loads?

[#8]

they are used here on the second tab of the query

BackLogSplit1:

LOAD *,

    applymap('Department_Map', MANAGER2, 'Other') as Department;

LOAD *,

    applymap('Division_Map', MANAGER2, 'Other') as Division;

SELECT

S.#SOSHP AS SHIPTONUMBER,

S.#SOSON AS ORDERNUMBER,

S.#SOSDE AS DATETOSHIP,

S.#SOFRT AS FREIGHT,

S.#SOSOS AS SHIPSEQUENCE,

(S.#SOSLS  * C.#COPCT) AS SALESPRICE,

S.#SOFBD AS BILLDATE,

S.#SOEDT AS DATEENTERED,

C.#COSMN AS SALESMAN,

C.#COPCT AS SPLITPCT1,

C.#COPC2 AS SPLITPCT2,

C.#COPC3 AS SPLITPCT3,

C.#COPC4 AS SPLITPCT4,

C.#COSON AS CUSTOMERNUMBER,

C.#COENT AS COMPANY,

A.#SLNAM AS SALESNAME,

A.#SLMG1 AS MANAGER1,

A.#SLMG2 AS MANAGER2,

SUBSTR(DIGITS(S.#SOSDT), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDT), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDT), 1, 4) AS SHIPDATE,

CASE

WHEN Date(SUBSTR(DIGITS(S.#SOSDE), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDE), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDE), 1, 4)) < CURRENT_DATE THEN Month(CURRENT_DATE)

WHEN Date(SUBSTR(DIGITS(S.#SOSDE), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDE), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDE), 1, 4)) IS NULL THEN Month(CURRENT_DATE)

ELSE Month(S.#SOSDE)

END AS SHIPMONTH,

CASE

WHEN Year(SUBSTR(DIGITS(S.#SOSDE), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDE), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDE), 1, 4)) < Year(CURRENT_DATE) THEN Year(CURRENT_DATE)

WHEN Year(SUBSTR(DIGITS(S.#SOSDE), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDE), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDE), 1, 4)) = Year(CURRENT_DATE) THEN Year(CURRENT_DATE)

WHEN Year(SUBSTR(DIGITS(S.#SOSDE), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDE), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDE), 1, 4)) > Year(CURRENT_DATE) THEN (Year(CURRENT_DATE)+1)

WHEN Year(SUBSTR(DIGITS(S.#SOSDE), 5, 2)||'/' || SUBSTR(DIGITS(S.#SOSDE), 7, 2)||'/'||SUBSTR(DIGITS(S.#SOSDE), 1, 4)) IS NULL THEN Year(CURRENT_DATE)

WHEN S.#SOSDE = 0 THEN Year(CURRENT_DATE)

END AS SHIPYEAR

FROM

NEPTUNE.SDDTA.SHPORD S

INNER JOIN

NEPTUNE.SDDTA.CUSORD C

ON

S.#SOSON = C.#COSON

INNER JOIN

NEPTUNE.SDDTA.SALESMAN A

ON

C.#COSMN = A.#SLSMN

WHERE

C.#COENT = 40

[#9]

I am assuming the Division is not linked to the first table and that's why I am getting the Zeros..

trying to figure out away to link it.

Should I create a new Spreadsheet with the BUSUNIT  on A , and Division on B and map it and load it?

I am not sure if that would work.

I appreciate your help If you can find me the right solution.

Thanks

[swuehl]

Could you post a small sample app? You could scramble the data or load mock up data if needed.