data in table but now flowing through to dashboard

[Anonymous]

hi,

the following data is a snap shot from the qlikview table.   the second snapshot shows that the Contract are loading but not the Returns (i.e. ConType in table).  How can I trouble shoot as to why the data is not coming through.  I have used Sum(AtFaults) in the expression.

[Anonymous]

even if I type in something simple like:-

if(%TeamKey<>1,if(ConType='Return',sum(AtFaults),0))
or

AtFaults

[jonathandienst]

I think we are stumbling about in the dark. I do not understand your post above at all.

Please post your qvw or at least a sample that clearly illustrates the problem. See this for help

Preparing examples for Upload - Reduction and Data Scrambling

[Anonymous]

sample data attached.

The data where ConType = Contract loads into the chart.  But

the data where ConType = "Return" does not load into the chart.

the script to load the above into the qlikview table is as follows:-

Facts:
LOAD
DateTrainingContractSigned as %DateKey,
'Contract' as ConType,
ConsultantTeam as %TeamKey,
FieldOfficerID as %UserKey,
FieldOfficerID as Consultant,
ContractID as ContractID,
1 as SUP#,
DateTrainingContractSigned as SignedOrRetMonth
      ;
     
SQL SELECT
FieldOfficer.id as "FieldOfficerID",
FieldOfficer.team_id as "ConsultantTeam",
tc.id as "ContractID",
ss.name as Status,
received_on,
signed_on as "DateTrainingContractSigned",
FieldOfficer.first_name ||' '|| FieldOfficer.surname as "FieldOfficer",
   FieldOfficer.role
from training_contracts tc
left join signup_statuses ss on tc.signup_status_id = ss.id
left join users FieldOfficer on FieldOfficer.id = tc.field_officer_id
Left join users AccountManager on AccountManager.id = tc.account_manager_id
left join users BDLRefferedBy on BDLRefferedBy.id = tc.bdl_referred_by_id
;

Concatenate (Facts)
LOAD
DateTrainingContractReturned as %DateKey,
'Return' as ConType,
TeamKey as %TeamKey,
FieldOfficerID as %UserKey,
FieldOfficerID as Consultant,
ContractID as ContractID,
ContractReturnID as ContractReturnID,
AtFaults,
FaultCode,
FaultName,
1 as SUPRet#,
DateTrainingContractReturned as SignedOrRetMonth;

Select
   training_contract_returns.id as "ContractReturnID",
   training_contract_id as "ContractID",
   training_contract_returns.at_fault as "AtFaults",
   training_contract_returns.created_at as "DateTrainingContractReturned",
   users.team_id as "TeamKey",
   training_contract_return_reason_types.id as "FaultCode",
   training_contract_return_reason_types.name as "FaultName",
   training_contract_returns.training_contract_id as "TrainContractKey",
   training_contracts.field_officer_id as "FieldOfficerID",
   training_contracts.field_officer_id as "Team"
     
from training_contract_returns 
    right join training_contract_return_reasons
    on training_contract_returns.id = training_contract_return_reasons.training_contract_return_id
right join training_contract_return_reason_types
    on training_contract_return_reasons.training_contract_return_reason_type_id = training_contract_return_reason_types.id
     right join training_contracts
    on training_contract_returns.training_contract_id = training_contracts.id
right join users
   on training_contracts.field_officer_id = users.id
  
where training_contract_returns.at_fault = 't';

[PrashantSangle]

Hi,

What is the relationship between Team Name and Provided xls sheet.

Provide detail information.

Regards,

[jonathandienst]

I am afraid I cannot devote the time to decode your script or construct a qvw to test your script. If you cannot post a qvw, then I will be unable to assist you.

[Anonymous]

Team Name and User are in separate tables.

the relationship between them is the TeamID:-

Consultants:
LOAD
team_id as %TeamKey,
id as %UserKey,
FieldOfficer as FOFullName,
role
;

SQL Select
users.id,
users.team_id,
users.first_name ||' '|| users.surname as "FieldOfficer",
users.job_title,
users.archived,
users.role
from users
;

Team:
LOAD
id as %TeamKey,
name as TeamName

;
SQL
SELECT * FROM teams
;

[Anonymous]

jonathan,  i loaded sample data from the facts table as i don't know how to load it into a qvw file.  when i tried to use the instructions you posted it told me it would permanently delete data.

[nizamsha]

Hi Laura,

Still Cant understand do u want to bring the

[nizamsha]

Hi Laura,

Still Cant understand what u want do u want to bring the TeamName column in Consultant Table if its so use applymap that what i understand from Post and

when to this code

Facts:

LOAD

DateTrainingContractSigned as %DateKey,

'Contract' as ConType,

ConsultantTeam as %TeamKey,

FieldOfficerID as %UserKey,

FieldOfficerID as Consultant,

ContractID as ContractID,

1 as SUP#,

DateTrainingContractSigned as SignedOrRetMonth;

Concatenate (Facts)

LOAD

DateTrainingContractReturned as %DateKey,

'Return' as ConType,

TeamKey as %TeamKey,

FieldOfficerID as %UserKey,

FieldOfficerID as Consultant,

ContractID as ContractID,

ContractReturnID as ContractReturnID,

AtFaults,

FaultCode,

FaultName,

1 as SUPRet#,

DateTrainingContractReturned as SignedOrRetMonth;

try to change the 2nd table column 2 as SUP# and check in the set analysis i am going blindly

check this one wheather its working for you

sum({$<ConType={"Contract"},SUP# ={'1'}>}contract)

sum({$<ConType={"Return"},SUP# ={'2'}>}ATFault)

[PrashantSangle]

Hi,

What is %TeamKey for Team Name  "Hunter Nor"

and

what is your expression for return.

Regards