Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am having issue loading the data from the SQL server as is into QVD. Seems like Qlikview is modifying the database value as it load into the QVD.
Example:
CaseNumber Row
00251256 1
00256456 2
00564867 3
00678978 4
.......
158964 1000
Qlikview will load the last row with 00158964 into the QVD.
However, when I specify in the where clause to load only row 1000, Qlikview will load 158964 into the QVD.
I tried the Verbatim = 1, and it doesn't work. Any idea?
Use Text() function like below,
SQL
SELECT
CustomInteraction_QueueUserReports.SegmentName,
CustomInteraction_QueueUserReports.EndCode,
CustomInteraction_QueueUserReports.ServerTs,
CustomInteraction_QueueUserReports.InteractionIDKey,
CustomInteraction_QueueUserReports.Direction,
CustomInteraction_QueueUserReports.ConnectionType,
CustomInteraction_QueueUserReports.Duration,
CustomInteraction_QueueUserReports.MEDIATYPE,
CustomInteraction_QueueUserReports.Queue,
Custom_PANAttributes.DateType,
Custom_PANAttributes.CaseTheatre,
Custom_PANAttributes.CaseServiceLevel,
Custom_PANAttributes."ASC",
Custom_PANAttributes.Priority,
Custom_PANAttributes.Role,
Custom_PANAttributes.SupportDeliveryType,
Custom_PANAttributes.AccountName,
Custom_PANAttributes.CaseProduct,
Custom_PANAttributes.CaseType,
Custom_PANAttributes.Location,
Custom_PANAttributes.ManagerName,
CustomInteraction_QueueUserReports.InteractionID,
CustomInteraction_QueueUserReports.FirstAssignedAcdSkillSet,
CustomInteraction_QueueUserReports.nTalk,
CustomInteraction_QueueUserReports.IsAcdAnswered,
CustomInteraction_QueueUserReports.LocalParty,
CustomInteraction_QueueUserReports.StartDateTimeUTC,
CustomInteraction_QueueUserReports.Disposition
FROM dbo.CustomInteraction_viw CustomInteraction_QueueUserReports INNER JOIN dbo.Custom_PANAttributes Custom_PANAttributes
How does your load statement look like?
Here is the whole script
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
set Verbatim = 1;
Set vQVDPath_S1 = '..\Stage 1 QVDs\';
connection string here......
Call_Custom_Queue:
SQL
SELECT
CustomInteraction_QueueUserReports.SegmentName,
CustomInteraction_QueueUserReports.EndCode,
CustomInteraction_QueueUserReports.ServerTs,
CustomInteraction_QueueUserReports.InteractionIDKey,
CustomInteraction_QueueUserReports.Direction,
CustomInteraction_QueueUserReports.ConnectionType,
CustomInteraction_QueueUserReports.Duration,
CustomInteraction_QueueUserReports.MEDIATYPE,
CustomInteraction_QueueUserReports.Queue,
Custom_PANAttributes.DateType,
Custom_PANAttributes.CaseTheatre,
Custom_PANAttributes.CaseServiceLevel,
Custom_PANAttributes."ASC",
Custom_PANAttributes.CaseNumber,
Custom_PANAttributes.Priority,
Custom_PANAttributes.Role,
Custom_PANAttributes.SupportDeliveryType,
Custom_PANAttributes.AccountName,
Custom_PANAttributes.CaseProduct,
Custom_PANAttributes.CaseType,
Custom_PANAttributes.Location,
Custom_PANAttributes.ManagerName,
CustomInteraction_QueueUserReports.InteractionID,
CustomInteraction_QueueUserReports.FirstAssignedAcdSkillSet,
CustomInteraction_QueueUserReports.nTalk,
CustomInteraction_QueueUserReports.IsAcdAnswered,
CustomInteraction_QueueUserReports.LocalParty,
CustomInteraction_QueueUserReports.StartDateTimeUTC,
CustomInteraction_QueueUserReports.Disposition
FROM dbo.CustomInteraction_viw CustomInteraction_QueueUserReports INNER JOIN dbo.Custom_PANAttributes Custom_PANAttributes
ON CustomInteraction_QueueUserReports.InteractionIDKey=Custom_PANAttributes.InteractionIDKey
WHERE
(Custom_PANAttributes.ManagerName='-' OR (Custom_PANAttributes.ManagerName>='!' AND Custom_PANAttributes.ManagerName<='zzz')) AND
(Custom_PANAttributes.Location='-' OR (Custom_PANAttributes.Location>='!' AND Custom_PANAttributes.Location<='zzz')) AND
(Custom_PANAttributes.CaseType='-' OR (Custom_PANAttributes.CaseType>='!' AND Custom_PANAttributes.CaseType<='zzz')) AND
(Custom_PANAttributes.CaseProduct='-' OR (Custom_PANAttributes.CaseProduct>='!' AND Custom_PANAttributes.CaseProduct<='zzz')) AND
(Custom_PANAttributes.AccountName='-' OR (Custom_PANAttributes.AccountName>='!' AND Custom_PANAttributes.AccountName<='zzz')) AND
(Custom_PANAttributes.SupportDeliveryType>='!' AND Custom_PANAttributes.SupportDeliveryType<='zzz') AND
(Custom_PANAttributes.Role='-' OR (Custom_PANAttributes.Role>='!' AND Custom_PANAttributes.Role<='zzz')) AND
(Custom_PANAttributes.Priority='-' OR (Custom_PANAttributes.Priority>='!' AND Custom_PANAttributes.Priority<='zzz')) AND
(Custom_PANAttributes.CaseNumber='-' OR (Custom_PANAttributes.CaseNumber>='!' AND Custom_PANAttributes.CaseNumber<='zzz')) AND
(Custom_PANAttributes."ASC"='-' OR (Custom_PANAttributes."ASC">='!' AND Custom_PANAttributes."ASC"<='zzz')) AND
(Custom_PANAttributes.CaseServiceLevel='-' OR (Custom_PANAttributes.CaseServiceLevel>='!' AND Custom_PANAttributes.CaseServiceLevel<='zzz')) AND
(Custom_PANAttributes.CaseTheatre='-' OR (Custom_PANAttributes.CaseTheatre>='!' AND Custom_PANAttributes.CaseTheatre<='zzz')) AND
(Custom_PANAttributes.DateType='-' OR (Custom_PANAttributes.DateType>='!' AND Custom_PANAttributes.DateType<='zzz')) AND
CustomInteraction_QueueUserReports.Queue<>'_systemroutinghub_' AND
((CustomInteraction_QueueUserReports.Queue>='!' AND CustomInteraction_QueueUserReports.Queue<='zzz') OR CustomInteraction_QueueUserReports.Queue='-' AND CustomInteraction_QueueUserReports.SegmentName='SGMNT CONFERENCE') AND
(CustomInteraction_QueueUserReports.MEDIATYPE>='!' AND CustomInteraction_QueueUserReports.MEDIATYPE<='zzz') AND
(CustomInteraction_QueueUserReports.ConnectionType=1 OR CustomInteraction_QueueUserReports.ConnectionType=2 AND CustomInteraction_QueueUserReports.Queue<>'-') AND
CustomInteraction_QueueUserReports.Direction=1 AND
(CustomInteraction_QueueUserReports.InteractionIDKey='-' OR (CustomInteraction_QueueUserReports.InteractionIDKey>='!' AND CustomInteraction_QueueUserReports.InteractionIDKey<='zzz')) AND
(CustomInteraction_QueueUserReports.SegmentName in ('SGMNT ACW','SGMNT ALERT','SGMNT CONFERENCE','SGMNT CONNECT','SGMNT DELAY','SGMNT HOLD')) AND
--(CustomInteraction_QueueUserReports.ServerTs>={ts '2015-07-01 07:00:00'} AND CustomInteraction_QueueUserReports.ServerTs<{ts '2015-07-09 07:00:00'}) AND
(CustomInteraction_QueueUserReports.FirstAssignedAcdSkillSet='-' OR (CustomInteraction_QueueUserReports.FirstAssignedAcdSkillSet>='!' AND CustomInteraction_QueueUserReports.FirstAssignedAcdSkillSet<='zzz')) AND
(CustomInteraction_QueueUserReports.LocalParty='-' OR (CustomInteraction_QueueUserReports.LocalParty>='!' AND CustomInteraction_QueueUserReports.LocalParty<='zzz'))
;
Store Call_Custom_Queue into $(vQVDPath_S1)Call_Custom_Queue.QVD;
drop table Call_Custom_Queue;
I can't find any part of your load script code that tries to filter and load only row 1000. So naturally QlikView will store all the rows in the QVD-file.
Even if you load data from DB then Qlikview syntax should be there
But try a luck, I know this is not useful but look once and then send that Qvd on wall
How path looks like for vQVDPath_S1
Store Call_Custom_Queue into $(vQVDPath_S1)Call_Custom_Queue.QVD (qvd);
I do not have a condition in the sql above to limit one row. If I do that, then I will definitely get a single row with CaseNumber 158964 stored in the QVD. Without the single row restriction, I get all the rows back including the CaseNumber 158964....but that CaseNumber is stored as 00158964 with two leading zeros. What parameter I need to put before the load to tell Qlikview to retrieve the CaseNumber as is without appending the two leading zeros? I think Qlikview automatically does it because it sees most preceding records have two leading zeros.
vQVDPath_S1 is just a C drive directory.
What I am trying to get is how do I force Qlikview load the data from SQL Server database as is without it modifying the actual values. It appends the two leading zeros to some values to the CaseNumber. The exact values in SQL Server database do not have leading zeros.
In fact, I didn't face any problem even with Postgresql. I doubt what was the for you
/*CUSTOM CONNECT TO Libraries over here(I don't want to disclose over here)
[FMP]:
SELECT "last_name",
"contact_id",
"first_name",
"country",
"Images"
FROM "Practice"."FMP";
STORE [FMP] into C:\Users\UserName\Downloads\FMP.qvd (qvd);*/
LOAD last_name,
contact_id,
first_name,
country,
Images
FROM
(qvd);
Can you check the same in SQL Server DB
Use Text() function like below,
SQL
SELECT
CustomInteraction_QueueUserReports.SegmentName,
CustomInteraction_QueueUserReports.EndCode,
CustomInteraction_QueueUserReports.ServerTs,
CustomInteraction_QueueUserReports.InteractionIDKey,
CustomInteraction_QueueUserReports.Direction,
CustomInteraction_QueueUserReports.ConnectionType,
CustomInteraction_QueueUserReports.Duration,
CustomInteraction_QueueUserReports.MEDIATYPE,
CustomInteraction_QueueUserReports.Queue,
Custom_PANAttributes.DateType,
Custom_PANAttributes.CaseTheatre,
Custom_PANAttributes.CaseServiceLevel,
Custom_PANAttributes."ASC",
Custom_PANAttributes.Priority,
Custom_PANAttributes.Role,
Custom_PANAttributes.SupportDeliveryType,
Custom_PANAttributes.AccountName,
Custom_PANAttributes.CaseProduct,
Custom_PANAttributes.CaseType,
Custom_PANAttributes.Location,
Custom_PANAttributes.ManagerName,
CustomInteraction_QueueUserReports.InteractionID,
CustomInteraction_QueueUserReports.FirstAssignedAcdSkillSet,
CustomInteraction_QueueUserReports.nTalk,
CustomInteraction_QueueUserReports.IsAcdAnswered,
CustomInteraction_QueueUserReports.LocalParty,
CustomInteraction_QueueUserReports.StartDateTimeUTC,
CustomInteraction_QueueUserReports.Disposition
FROM dbo.CustomInteraction_viw CustomInteraction_QueueUserReports INNER JOIN dbo.Custom_PANAttributes Custom_PANAttributes
Wish there is a way to set a parameter to store the value as is without turning the specific fields to text to preserve the actual values from the source database.