Fails to connect to Database

Report Inappropriate Content · ‎2016-04-28

I have a tab within the load script and the whole report fails as this part fails to connect to the Database. It will connect to the PROMOTION_REDEMPTION__C table in the same Database but wont connect to the CAMPAIGN one:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=vrSalesforceVR1;Data Source=B2CSQL-01;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BIC007033;Use Encryption for Data=False;Tag with column collation when possible=False];

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 Distinct ID,
NAME,
    TYPE
FROM vrSalesforceVR1.dbo.CAMPAIGN;

Gysbert_Wassenaar · ‎2016-04-28

If you can't connect to that table then you should get an error. That error should give you information about the cause. Perhaps you don't have the correct privileges to access the table. Or maybe it has a slightly different name.

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2016-04-29

Thank you for coming back to me. The error I get is shown below:

Gysbert_Wassenaar · ‎2016-04-29

Ok, your screenshot shows that you can connect to the database and read data from that table. Perhaps one of the fields contains something that trips up the oledb driver. The problem is somewhere between the oledb driver and the database. Qlikview itself isn't causing it. Unfortunately that's all I can say about it. Perhaps your local friendly database administrator can determine on the database side what's going on.

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2016-05-03

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;

Kushal_Chawda · ‎2016-05-03

I think there is some field in your CAMPAIGN table which is causing this issue. Try to comment some field randomly (ID or NAME or TYPE) to figure out which field is causing issue. Probably this is Oledb driver issue.

You can also try to change the connection string to ODBC and check.

Report Inappropriate Content · ‎2016-05-06

Thank you so much. It worked.

Was the TYPE that was causing the issue.

Related Topics