topic Re: Load value from database as is in QlikView

Load value from database as is

Anonymous — Thu, 16 Feb 2017 03:01:35 GMT

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?

Re: Load value from database as is

petter — Thu, 16 Feb 2017 03:09:50 GMT

How does your load statement look like?

Re: Load value from database as is

Anonymous — Thu, 16 Feb 2017 03:14:58 GMT

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;

Re: Load value from database as is

petter — Thu, 16 Feb 2017 03:21:48 GMT

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.

Re: Load value from database as is

Anil_Babu_Samineni — Thu, 16 Feb 2017 03:30:37 GMT

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

Re: Load value from database as is

Anonymous — Thu, 16 Feb 2017 05:15:25 GMT

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.

Re: Load value from database as is

Anonymous — Thu, 16 Feb 2017 05:19:25 GMT

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.

Re: Load value from database as is

Anil_Babu_Samineni — Thu, 16 Feb 2017 05:39:14 GMT

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

Re: Load value from database as is

sasikanth — Thu, 16 Feb 2017 05:49:25 GMT

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

Re: Load value from database as is

Anonymous — Thu, 16 Feb 2017 18:08:55 GMT

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.