Re: Fails to connect to Database

Tue, 03 May 2016 09:59:44 GMT

Good morning

I have now changed the Campaign Script to try an limit the amount of data being brought in:

Load ID as REDEEMABLE_CAMPAIGN__C,

NAME as campaign_NAME,

TYPE,

CREATEDDATE;

SQL SELECT

,[NAME]

,[TYPE]

,[CREATEDDATE]

FROM .[dbo].[CAMPAIGN]

Where CREATEDDATE >= Convert(datetime, '2016-04-01');

But now the report stops at the second tab Outliers:

//F123_Transactions:

//LOAD * FROM

//[..\STDQVD\FOOTFALL123\F123_Transactions.qvd](qvd);

// F123Brands:

// LOAD

// Distinct

// FK_Brand as F123Brand

// Resident F123_Transactions;

// Sales:

// Load Distinct

// RowNo()&'-'&PK_F123_Transactions as PK_Sales,

// FK_Brand,

// 'xyz' as Outlier_Department,

// FK_Village,

// FK_Calendar,

// 0 as ,

// 0 as

// Resident F123_Transactions

// WHERE WeekName(FK_Calendar) = WeekName(Today()-1);

// Concatenate (Sales)

Sales:

LOAD

RowNo() as PK_Sales,

FK_Village,

FK_Brand,

FK_Department as Outlier_Department,

// FK_Segment,

// FK_Gender,

// FK_MarketPosition,

// FK_InternationalPosition,

FK_Calendar,

Sum() as ,

Sum(TRANSACTIONS) as

FROM

(qvd)

WHERE FK_Calendar >= '$(vReportStartDate)'

//Only load F123 brands

//AND Exists(F123Brand, FK_Brand)

GROUP BY FK_Village, FK_Department, FK_Segment, FK_Gender, FK_MarketPosition, FK_InternationalPosition, FK_Brand, FK_Calendar

;

//DROP Tables F123_Transactions,F123Brands;

Temp:

LOAD FK_Village,

FK_Calendar,

FK_Brand,

Outlier_Department,

sum() as

Resident Sales

Group by FK_Village,FK_Calendar,FK_Brand,Outlier_Department;

DROP Table Sales;

Temp1:

NoConcatenate

LOAD *,

WeekDay(FK_Calendar) as Day,

WeekName(FK_Calendar) as Week

Resident Temp where >0;

DROP Table Temp;

Temp2:

NoConcatenate

Load *

Resident Temp1 Order By FK_Village,FK_Brand,Day,Week;

Drop Table Temp1;

Temp3:

NoConcatenate

Load *,

if(>0,if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day),peek(Counter)+1,1),peek(Counter)) as Counter,

RangeSum(,if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day),peek('CumAmt'),FK_Brand)) as CumAmt

Resident Temp2 ;

DROP Table Temp2;

Temp4:

LOAD *,

if(Counter>13,(CumAmt-peek('CumAmt',-13))/13,CumAmt/13) as Average,

RangeStdev(,if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day),peek(),FK_Brand),if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day),peek(,-2),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-3),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-4),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-5),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-6),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-7),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-8),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-9),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-10),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-11),FK_Brand),

if(FK_Brand=Previous(FK_Brand)and Day=Previous(Day) ,peek(,-12),FK_Brand)

) as stddev

Resident Temp3;

DROP Table Temp3;

Temp5:

LOAD *,

Average+stddev*2 as Outlier_Comp

Resident Temp4;

Left Join

LOAD PK_Brand as FK_Brand,

BRAND_NAME

FROM

(qvd);

DROP Table Temp4;

Outlier:

LOAD

FK_Village&Upper()&Day&Week as %key1,

Outlier_Department,

Average,

stddev,

Outlier_Comp

Resident Temp5;

Drop Table Temp5;

STORE Outlier into ;

DROP Table Outlier;

Tab 3 - Main Tables and Links

//LOAD TABLES

//Common Tables

Calendar:

LOAD

FROM (qvd)

WHERE CAL_DATE >= '$(vReportStartDate)';

Village:

//For PartnerProgram Data reduction

LOAD

PK_Village AS PK_Village,

FROM (qvd);

//Footfall123 Tables

//Dimension Tables

//CALL LoadSTDQVDFootfall123Table('F123_Country') ;

F123_Country:

LOAD PK_F123_Country,

FROM

(qvd);

CALL LoadSTDQVDFootfall123Table('F123_Promotion');

F123_Contact:

LOAD PK_F123_Contact,

FK_F123_Contact_Country as F123_Contact_Country

FROM

(qvd);

F123_Contact_Country:

LOAD PK_F123_Contact_Country as F123_Contact_Country,

FROM

(qvd);

//Transactions Tables

//CALL LoadSTDQVDFootfall123Table('F123_Transactions');

F123_Transactions_Temp:

LOAD *,

if(Week(FK_Calendar)=week(Today()-1),FK_Village&FK_Brand&WeekDay(FK_Calendar)&Weekname(date(WeekName(FK_Calendar)-1)),

FK_Village&FK_Brand&WeekDay(FK_Calendar)&WeekName(FK_Calendar)) as %key1

FROM

(qvd);

left join

LOAD %key1,

Average,

stddev,

Outlier_Comp,

Outlier_Department

FROM

(qvd);

F123_Transactions:

Load *,

if((<1 and Outlier_Department='D') or >Outlier_Comp or IsNull(Outlier_Comp),'Outlier','Not Outlier') as OutlierFlag

Resident F123_Transactions_Temp;

DROP Table F123_Transactions_Temp;

F123Brands:

LOAD

Distinct

FK_Brand&FK_Village as F123Brand

Resident F123_Transactions;

Sales:

LOAD

RowNo() as PK_Sales,

FK_Village,

FK_Brand,

FK_Department,

FK_Segment,

FK_Gender,

FK_MarketPosition,

FK_InternationalPosition,

FK_Calendar,

Sum() as ,

Sum(TRANSACTIONS) as

FROM

(qvd)

WHERE FK_Calendar >= '$(vReportStartDate)'

//Only load F123 brands

AND Exists(F123Brand, FK_Brand&FK_Village)

GROUP BY FK_Village, FK_Department, FK_Segment, FK_Gender, FK_MarketPosition, FK_InternationalPosition, FK_Brand, FK_Calendar;

Lease:

LOAD ,

PK_Lease,

as FK_Village,

as FK_Brand,

FROM

(qvd);

BrandSalesFactors:

LOAD *

FROM

(qvd);

//STDQVDs

//Dimension Tables

CALL LoadSTDQVDTable('Brand');

CALL LoadSTDQVDTable('Department');

CALL LoadSTDQVDTable('Segment');

CALL LoadSTDQVDTable('Gender');

CALL LoadSTDQVDTable('MarketPosition');

CALL LoadSTDQVDTable('InternationalPosition');

Concatenate(Brand)

LOAD

'UNKNOWN' as PK_Brand,

'UNKNOWN' as ,

'Unknown' as

AutoGenerate(1);

Concatenate(Department)

LOAD

'UNKNOWN' as PK_Department,

'UNKNOWN' as ,

'Unknown' as

AutoGenerate(1);

Concatenate(Segment)

LOAD

'UNKNOWN' as PK_Segment,

'UNKNOWN' as ,

'Unknown' as

AutoGenerate(1);

Concatenate(Gender)

LOAD

'UNKNOWN' as PK_Gender,

'UNKNOWN' as ,

'Unknown' as

AutoGenerate(1);

Concatenate(MarketPosition)

LOAD

'UNKNOWN' as PK_MarketPosition,

'UNKNOWN' as ,

'Unknown' as

AutoGenerate(1);

Concatenate(InternationalPosition)

LOAD

'UNKNOWN' as PK_InternationalPosition,

'UNKNOWN' as ,

'Unknown' as

AutoGenerate(1);

DROP TABLE F123Brands;

Footfall:

LOAD

PK_Footfall,

FK_Village,

FK_Calendar,

// ,

FROM (qvd);

//Left Join (F123_Transactions)

//Load

// barcode_number as ,

// CP_ID as CP_ID

// ;

//SQL SELECT "barcode_number" ,

// "CP_ID"

//FROM vrRob.QV."Transaction_Fact";

//Campaign:

//SQL SELECT "CP_B2C_CAMPAIGN_ID__C",

// "CP_CAMPAIGN_VILLAGE_S__C",

// "CP_ID",

// "CP_name",

// "CP_rownum",

// "CP_salesForceExtractCampaignID",

// "CP_START_MONTH__C",

// "CP_START_YEAR__C",

// "CP_TARGET_AUDIENCE__C"

//FROM vrRob.QV."dim_Campaign"

//where CP_START_YEAR__C = '2013'

//and CP_START_MONTH__C >= '7'

//and CP_name like '%Private Sale%'

//or CP_name like '%Private-Sale%';

///// Live Campaign data pull

\

//Campaign:

//Load *,

// barcode as ,

// Date as CP_Date,

// village as CP_Village

// ;

//SQL SELECT *

//FROM vrRob.QV."Customer_Campaign_Details"

//where Date >= '01/07/2013'

//or Date is null;

// *** Commented out as per Jon Hersom Approval 03-06-2015 ***

// *** Comment Start ***

//Campaign:

//LOAD *

//FROM

//[
qlikview.valueretail.com\qlikview\live\STDQVD\FOOTFALL123\Campaign.qvd](qvd);

//CampEmail:

//Load

// campaignID,

// TPET_touchPointEventTypeName as TPET_Name,

// village as CE_Village,

// Date as CE_Date,

// Hour as CE_Hour,

// Count as CE_Type_Count

//;

//SQL SELECT

// DTPET.TPET_touchPointEventTypeName

// --, campaignName,

// , tpf.campaignID

// ,

// , DD.Date

// , DT.[Hour]

// --, DT.AmPm

// , COUNT(*) AS

// FROM

// .[QV].[TouchPoint_Fact] TPF

// JOIN vrRob.QV.dim_Date AS DD ON TPF.DateID = DD.d_DateID

// JOIN vrRob.QV.dim_Time AS DT ON TPF.TimeID = DT.t_TimeID

// JOIN vrRob.QV.dim_TouchPoint AS DTP ON TPF.touchPointID = DTP.TP_TouchPointID

// JOIN vrRob.QV.dim_TouchPointType AS DTPT ON DTP.TP_touchPointTypeID = DTPT.TPT_touchPointTypeID

// JOIN vrRob.QV.dim_TouchPointEventType AS DTPET ON TPF.touchPointEventTypeID = DTPET.TPET_touchPointEventTypeID

// JOIN ( SELECT

// a.customerBarcodeID

// , a.customerid

// , barcode

// FROM

// vrB2C.dbo.CustomerBarcode A

// JOIN ( SELECT

// MAX(customerBarcodeID) AS customerBarcodeID

// , customerid

// FROM

// vrB2C.dbo.CustomerBarcode

// GROUP BY

// customerID

// ) B ON A.customerBarcodeID = B.customerBarcodeID

// AND A.customerID = B.customerid

// WHERE

// isDeleted = 0

// ) AS CB ON TPF.customerID = CB.customerID

// WHERE

// DTPT.TPT_touchPointTypeName = 'Email'

// AND DTP.TP_touchPointName = 'Responsys'

// GROUP BY

// DTPET.TPET_touchPointEventTypeName

// , campaignid

// ,

// , DD.Date

// , DT.[Hour]

// --, DT.AmPm

//;

// *** Commented out as per Jon Hersom Approval 03-06-2015 ***

// *** Comment End ***

CustomerLSTxt:

Load barcode as ,

leadSourceText

;

SQL SELECT barcode,

leadSourceText

FROM vrRob.QV."dim_Customer";

Tab 4 - Create Links

//Create Link tables and Duplicate Keys

CALL CreateLinkTable;

CALL CheckDuplicateKeys;

Tab 5 - Images

Images:

BUNDLE INFO LOAD

'
qlikview.valueretail.com\qlikview\live\IMAGES2\'& &'-Logo.jpg' AS Image_Path

RESIDENT Village;

// Selection of Currency

FxCurrency:

LOAD

Currency,

CurSymbol,

ExchangeCurrency,

ExchangeRate

// Dollar

FROM
qlikview.valueretail.com\qlikview\live\STDQVD\FxCurrency.qvd (qvd)

Where Currency <> 'Dollar'

and ExchangeCurrency <> 'Dollar'

;

//// Selection of m2 or Sqft //

//UnitArea:

//LOAD * INLINE [

// UnitMeasure, UnitConversion

// m2, 1

// Sqft, 10.7639

//];

Tab 6 - BATS

OLEDB CONNECT TO ;

BATS:

LOAD distinct promotionRedemptionID,

ID as Redemption_ID,

"REDEEMABLE_CAMPAIGN__C",

'SF Tagged' as BATS_Flag,

"VOUCHER_CODE__C" as barcode_number,

"VOUCHER_CODE__C" as Redemption_barcode_number

;

SQL SELECT promotionRedemptionID,

ID,

"REDEEMABLE_CAMPAIGN__C",

"VOUCHER_CODE__C"

FROM vrSalesforceVR1.dbo."PROMOTION_REDEMPTION__C";

Left Join (BATS)

//LOAD ID as REDEEMABLE_CAMPAIGN__C,

//NAME as campaign_NAME,

//TYPE;

//SQL SELECT ID,

// NAME,

// TYPE

//FROM vrSalesforceVR1.dbo.CAMPAIGN;

LOAD Distinct

ID as REDEEMABLE_CAMPAIGN__C,

NAME as campaign_NAME,

TYPE,

CREATEDDATE;

SQL SELECT

,[NAME]

,[TYPE]

,[CREATEDDATE]

FROM .[dbo].[CAMPAIGN]

Where CREATEDDATE >= Convert(datetime, '2016-04-01');

Tab 7 - Section Access

LET vApplication = 'B2C';

LET vApplicationName = Left(DocumentName(),Len(DocumentName())-7);

$(Include=..\SECURITY\QV-Security-Script.txt);

Tab 8 - Close Up

Drop Table Tmp_Villages;

Drop Table Tmp_F123_Transactions_Registration_Status;

Tab 9 - Exit Script

exit Script;

topic Re: Fails to connect to Database in QlikView

Fails to connect to Database

Re: Fails to connect to Database

Re: Fails to connect to Database

Re: Fails to connect to Database

Re: Fails to connect to Database

Re: Fails to connect to Database

Re: Fails to connect to Database