Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
Showing results for 
Search instead for 
Did you mean: 

Resident table not refreshing

Hi All,

I have the below script. Where the resident table is not refreshing when I load the data. I have two tables Opportunity and Win tables. Can anyone help me with this issue? When I pull the data for Won Opportunity (Sum({< [Win Marketing Campaign Flag]={'1'}>}[Converted Win Amount])). I don't seem to get the right data.

LOAD Id as "Opportunity ID",
AccountId as "Account ID",
    OwnerId as "Opportunity Owner ID",
    Name as "Opportunity Name",
    Lookup('CR', 'IsoCode', [CurrencyIsoCode],'Currency') as "ConversionRate",
    Amount/(Lookup('CR','IsoCode',[CurrencyIsoCode],'Currency')) as "Converted Amount",
Date(floor(CloseDate)) as "Close Date",
     "Type" as "Opportunity Type",

    CampaignId as "Opportunity Campaign ID",
    Lookup('Lead Campaign Name','Id',[CampaignId],'Campaign') as "Opportunity Campaign",
    Lookup('Lead Parent Campaign','Id',[CampaignId],'Campaign') as "Opportunity Parent Campaign",
    Lookup('Originating_Group__c','Id',[CampaignId],'Campaign') as "Opportunity Campaign Group",
    Lookup('LeadMarketingCampaignFlag','Id',[CampaignId],'Campaign') as "Opportunity Marketing Campaign Flag",

Date(floor(CreatedDate)) as "Opportunity Created Date",
    Year(CreatedDate) as "Opportunity Year",
    Month(CreatedDate) as "Opportunity Month",
    Year([CreatedDate])& Dual('Q' & Ceil(Month([CreatedDate])/3), Ceil(Month([CreatedDate])/3)) as ScorecardQuarter,
    Opportunity_Status__c as "Opportunity Status",
Oppty_from_Lead__c as "Opportunity from Lead",
    if(Oppty_from_Lead__c>0,'Marketing Sourced', 'Marketing Influenced') as "Mktg Sourced vs Influenced Flag",
floor(CloseDate-CreatedDate) as "Opportunity Age",

    Lookup('TempRegion', 'OwnerId', [OwnerId],'Owner') as "Region",
    Lookup('TempSubTheater', 'OwnerId', [OwnerId],'Owner') as "Sub Theater",

    Lookup('User_Sales_Group__c', 'OwnerId', [OwnerId],'Owner') as "Opportunity Sales Group",
    Lookup(' User_Sales_Region__c,', 'OwnerId', [OwnerId],'Owner')as "Opportunity Sales Region",
    if(Len(Trim("CampaignId"))>0,1,0) as "Opportunity Campaignflag",
    If([Type]<>'Sales Out - from stock','EMEArevenueTypeFlag','Other') AS EMEArevenueType_noSalesOut_Flag,
FROM [lib://Salesforce QVD/Opportunity.qvd](qvd) where CloseDate<=332704 and Year(CreatedDate)>2012
and ([Include_in_Standard_Global_Pipeline__c]=1 or Match([Type],'Sales Out - from stock'));

//This is a new table from opportunities, a subset for those won
   "Opportunity ID" as "Win Opportunity ID",
   "Opportunity Owner ID" as "Win Opportunity Owner ID",
   "Close Date" as "Win Date",
   floor("Close Date" - "Opportunity Created Date") as "Win Age",
   "Converted Amount" as "Converted Win Amount",
   "Opportunity Campaign ID" as "Win Campaign ID",
   "Opportunity Campaign Group" as "Win Campaign Group",
   "Opportunity Marketing Campaign Flag" as "Win Marketing Campaign Flag",
   "Opportunity Type" as "Win Opportunity Type",
   "Opportunity from Lead" as "Win Opportunity from Lead",

   Year([Close Date])& Dual('Q' & Ceil(Month([Close Date])/3), Ceil(Month([Close Date])/3)) as ScorecardQuarter
  Resident Opportunity where "Opportunity Status"='Closed - Won' and Year([Close Date])>=2012;

3 Replies

A small sample of data will help to figure out the script issue.

Master III
Master III

Please share sample data


I suggest you to load Opportunity table only with two parameters Opportunity Marketing Campaign Flag and Converted Amount.

Try out your formula:

Sum({< [Opportunity Marketing Campaign Flag]={'1'}>} [Converted Amount]))

If that do not work then something wrong if lookup. If it works that it might be resident problems that I doubt.