Trouble pulling all records from SQL View

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am working with pulling data from some SQL views and for some reason, some records are pulling in while some are not, or if they are, they are not displaying in my chart (some do though). Stuck to say the least.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='th:mm:ss';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY th: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';

//ODBC CONNECT TO [Business Data - PROD] (UserId is proddta, Password is proddta);

ODBC CONNECT TO JDE (XUserId is EMeKSZFMWH, XPassword is DYdPKbcNPDZITZFOMLIcL);

WorkOrderMasterFile:

load

WADOCO as WorkOrder,

WARORN as SONumber,

WASRST as StatusCode,

WATYPS as TypeWo,

WAMMCU as Branch,

WASTRT as DateStart,

WASTRX as CompletionDate,

WALITM as ItemNumber,

WAITM as ItemNumberShort,

WAUOM as UOM,

WADL01 as WODescpription,

WASOBK as [F4801 UnitsQuanBackorHeld],

WASOCN as [F4801 UnitsQuantityCanceled],

WASOQS/10000 as [F4801 UnitsQuantityShipped],

WATRDJ as DateTransactionJulian,

WLMCU as WorkCenter,

WLDSC1 as WCDesc,

Round((WLRUNL*60)/3600,0.01) as [Std Run Time],

Round((WLSETL*60)/3600,0.01) as [Std Setup Time],

fpmaverage as pressspeed;

SQL SELECT *

FROM "JDE_PRODUCTION".dbo.VISION_MAINWOPULL;

TimeTrans:

Load

RowNo() as RowID,

WTDOCO as WorkOrder,

WTAN8 as EmployeeNumber,

WTMMCU as Branch,

WTMCU as WorkCenter,

//STATUS as ReasonCode,

WTPBTM as trans_begintime,

WTPETM as trans_endtime,

WTSHFT as shift,

If(Len(text(WTDGL))>5,

Date(MakeDate(1900 + (left(text(WTDGL),1) * 100) + mid(text(WTDGL),2,2)) + (right(text(WTDGL),3) - 1)),

Date(MakeDate(1000 + (left(text(WTDGL),1) * 100) + mid(text(WTDGL),1,2)) + (right(text(WTDGL),3) - 1))) as [Employee Work Date],

WTDGL as JulianWorkDate,

WTHRW / 100 as trans_hrs,

STATUS as timecode,

YAALPH as NameAlpha;

SQL SELECT *

FROM "JDE_PRODUCTION".dbo.VISION_WOTIMETRANS;

There are multple records of the TimeTrans table to each record in the WorkOrderMasterFile

Re: Trouble pulling all records from SQL View

Anil_Babu_Samineni — Fri, 02 Nov 2018 03:29:41 GMT

Can you debug using NoOfRows() and by help of Trace function whether how many records are fetching and try same with Oracle as well as you need.

Re: Trouble pulling all records from SQL View

Anonymous — Fri, 02 Nov 2018 18:00:13 GMT

Great - thank you - I was able to verify that the count between the records pulled in the view and pulled into Qlikview are the same amount of records - so the problem i presume is in how I am creating the chart? I can see the records in the SQL View through a query but not in Qlikview.

Re: Trouble pulling all records from SQL View

juraj_misina — Fri, 02 Nov 2018 22:32:01 GMT

So what exactly is the problem then? Can you post a screenshot, sample app or more detailed description of the issue?

Re: Trouble pulling all records from SQL View

Anil_Babu_Samineni — Mon, 05 Nov 2018 09:24:16 GMT

No, you can not do perform SQL Queries into Chart using Qlik platform. What are you trying?

topic Re: Trouble pulling all records from SQL View in QlikView

Trouble pulling all records from SQL View

Re: Trouble pulling all records from SQL View

Re: Trouble pulling all records from SQL View

Re: Trouble pulling all records from SQL View

Re: Trouble pulling all records from SQL View