Load value from database as is

[Anonymous]

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?

[petter]

How does your load statement look like?

[Anonymous]

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;

[petter]

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.

[Anil_Babu_Samineni]

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);

[Anonymous]

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. 

[Anonymous]

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.

[Anil_Babu_Samineni]

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

[sasikanth · Accepted solution]

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

  Text(Custom_PANAttributes.CaseNumber) As 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

[Anonymous]

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.