Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor III

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
Honored Contributor III

Re: Resident table not refreshing

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

Honored Contributor III

Re: Resident table not refreshing

Please share sample data

Valued Contributor II

Re: Resident table not refreshing

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.