Resident table not refreshing

Anonymous — Wed, 10 May 2017 23:09:22 GMT

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.

Opportunity:
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,
    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,
    Include_in_Standard_Global_Pipeline__c
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
   Win:
   Load
   "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",

"Region",
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;

Re: Resident table not refreshing

Digvijay_Singh — Thu, 11 May 2017 03:11:27 GMT

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

Re: Resident table not refreshing

prma7799 — Thu, 11 May 2017 03:41:30 GMT

Please share sample data

Re: Resident table not refreshing

MindaugasBacius — Thu, 11 May 2017 12:56:23 GMT

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.

topic Re: Resident table not refreshing in QlikView

Resident table not refreshing

Re: Resident table not refreshing

Re: Resident table not refreshing

Re: Resident table not refreshing