Hey Jonathan
Could you give me please some more assistance?
My Load scripts looks like below:
Main:
SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET MoneyFormat='CHF#''##0.00;-CHF#''##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='D.MM.YY';
SET TimestampFormat='D.MM.YY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
LET homeCountry='Switzerland';
/* customised settings */
SET range_red = 0.75;
SET color_red = '220, 0, 0';
SET range_orange = 0.95;
SET color_orange = '255, 129, 0';
SET color_green = '0, 230, 0';
SET color_gray = '150, 150, 150';
SET success_lup = count({<lupsuccess = {'OK'}>}lupsuccess)/count({<lupsuccess ={'OK','NOK'}>}lupsuccess);
SET success_voice = count({<tcvoicesuccess = {'OK'}>}tcvoicesuccess)/count({<tcvoicesuccess ={'OK','NOK'}>}tcvoicesuccess);
SET success_sms = count({<tcsmssuccess = {'OK'}>}tcsmssuccess)/count({<tcsmssuccess ={'OK','NOK'}>}tcsmssuccess);
SET success_data = count({<tcdatasuccess = {'OK'}>}tcdatasuccess)/count({<tcdatasuccess ={'OK','NOK'}>}tcdatasuccess);
countries:
LOAD
iso as iso3166a,
name,
longitude,
latitude
FROM [lib://CommonData (nexus-ag_blappur)/countries.csv]
(txt, codepage is 1252, embedded labels, delimiter is '$', msq);
World:
LOAD
world.Name as iso3166a,
world.Area
FROM [lib://CommonData (nexus-ag_blappur)/world.kml]
(kml, Table is [World.shp/Features]);
Database:
LIB CONNECT TO 'PostgreSQL35W';
LOAD tcsuccess,
lupsuccess,
callid,
cycle,
faultid,
faulttext,
Timestamp("time") as "time",
simoperator,
country,
shortcountry as iso3166a,
nwoperator,
lupReq,
lupAcc,
testCaseName,
campaignname,
msisdn,
imsi,
callednr,
LocUpDur,
'LUP' as testtype;
SQL SELECT tcsuccess,
lupsuccess,
callid,
cycle,
faultid,
faulttext,
"time",
simoperator,
country,
shortcountry,
nwoperator,
"lupReq",
"lupAcc",
"testCaseName",
campaignname,
msisdn,
imsi,
callednr,
"LocUpDur",
'LUP' as testtype
FROM mobile."public".lupkpi;
JOIN
LOAD tcdatasuccess,
callid,
cycle,
faultid,
faulttext,
Timestamp("time") as "time",
simoperator,
country,
shortcountry as iso3166a,
nwoperator,
testCaseName,
campaignname,
msisdn,
imsi,
callednr,
actpdpreq,
actpdpacc,
UpTp,
DnTp,
PdpCtxActDur,
servername,
'Data' as testtype;
SQL SELECT tcdatasuccess,
callid,
cycle,
faultid,
faulttext,
"time",
simoperator,
country,
shortcountry,
nwoperator,
"testCaseName",
campaignname,
msisdn,
imsi,
callednr,
actpdpreq,
actpdpacc,
"UpTp",
"DnTp",
"PdpCtxActDur",
servername,
'Data' as testtype
FROM mobile."public".datakpi;
LOAD tcsmssuccess,
callid,
cycle,
faultida,
faulttexta,
faultidb,
faulttextb,
Timestamp("time") as "time",
simoperatora,
simoperatorb,
countrya,
shortcountrya,
nwoperatora,
countryb,
shortcountryb,
nwoperatorb,
testCaseName,
msisdna,
imsia,
callednr,
smssend,
smsrec,
smstextb,
SmsDelivery,
If(countrya='$(homeCountry)',countryb,countrya) as country,
If(countrya='$(homeCountry)',nwoperatorb,nwoperatora) as nwoperator,
If(countrya='$(homeCountry)',shortcountryb,shortcountrya) as iso3166a,
'SMS' as testtype;
SQL SELECT tcsmssuccess,
callid,
cycle,
faultida,
faulttexta,
faultidb,
faulttextb,
"time",
simoperatora,
simoperatorb,
countrya,
shortcountrya,
nwoperatora,
countryb,
shortcountryb,
nwoperatorb,
"testCaseName",
msisdna,
imsia,
callednr,
smssend,
smsrec,
smstextb,
"SmsDelivery",
'SMS' as testtype
FROM mobile."public".smskpi;
JOIN
LOAD tcvoicesuccess,
callid,
cycle,
faultida,
faulttexta,
faultidb,
faulttextb,
Timestamp("time") as "time",
simoperatora,
simoperatorb,
countrya,
shortcountrya,
nwoperatora,
countryb,
shortcountryb,
nwoperatorb,
testCaseName,
msisdna,
imsia,
callednr,
mosa,
mosb,
setupa,
setupb,
connectack,
disconnecta,
"CallSetupDur",
If(countrya='$(homeCountry)',countryb,countrya) As country,
If(countrya='$(homeCountry)',nwoperatorb,nwoperatora) as nwoperator,
If(countrya='$(homeCountry)',shortcountryb,shortcountrya) as iso3166a,
'Voice' as testtype;
SQL SELECT tcvoicesuccess,
callid,
cycle,
faultida,
faulttexta,
faultidb,
faulttextb,
"time",
simoperatora,
simoperatorb,
countrya,
shortcountrya,
nwoperatora,
countryb,
shortcountryb,
nwoperatorb,
"testCaseName",
msisdna,
imsia,
callednr,
mosa,
mosb,
setupa,
setupb,
connectack,
disconnecta,
"CallSetupDur",
'Voice' as testtype
FROM mobile."public".voicekpi;
As far as I understood you. I've to extend the line below
LIB CONNECT TO 'PostgreSQL35W';
to something like:
LIB CONNECT TO 'PostgreSQL35W' UID=userid PWD=password;
and put this into the QVD file?
.png "Former Employee"){kind=icon}