join problem

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

Hi guys

i have to read 5 views from sql in qlikview, i joined them like bellow, it has no error when i write top 1000, but when i run in sql the records got to millions and took sooooo long.

are my joins have problem? if yes i would appreciate if you give me the correct join in my case.

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=IT;Initial Catalog=MMTDB;Data Source=192.168.20.22;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SEPASGOL;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is AAfMXREJUKTaEKNEDG);

Combined:

//-------- Start Multiple Select Statements ------

SQL SELECT top 1000

VUnit.Active U_Active,

VUnit.CityName as U_CityName,

VUnit.CreateUnitName as U_CreateUnitName,

VUnit.DeliveryDate as U_DeliveryDate,

VUnit.HixCode as U_HixCode,

VUnit.MobileAdmin as U_MobileAdmin,

VUnit.OwnershipType as U_OwnershipType,

VUnit.PhoneNumber as U_PhoneNumber,

VUnit.State as U_State,

VUnit.UnitCode,

VUnit.UnitType,

VUser.father as User_father,

VUser.FName as User_FName ,

VUser.Id as User_ID,

VUser.IsActive as ,

VUser.LName as UserLastName,

VUser.RegisterDateTime,

VUser.RoleName,

VUser.UnitName,

VUser.UserName,

VSick.Address,

VSick.Age,

VSick.BirthDate,

VSick.CityId,

VSick.CityName,

VSick.Diploma,

VSick.DiseaseList,

VSick.DrugId as SickDrugID,

VSick.DrugName as SickDrugName,

VSick.EndDateCourse,

VSick.father,

VSick.FileNumber,

VSick.FirstDateUse,

VSick.FName as SickFName,

VSick.Id ,

VSick.IDNumber,

VSick.LastStateComment,

VSick.LastStateDate,

VSick.LName as SickLName,

VSick.Marital,

VSick.Mobile,

VSick.NarcoticList,

VSick.NationalCardNumber,

VSick.NationalityCaption,

VSick.NationalityID,

VSick.PhoneNumber,

VSick.RangeAgeCaption,

VSick.RangeAgeId,

VSick.Sexual,

VSick.SickStateId,

VSick.SickStateLatinName,

VSick.SickStateName,

VSick.SickTypeId,

VSick.SickTypeLatinName,

VSick.SickTypeName,

VSick.Skill,

VSick.StartDateCourse,

VSick.Tribe,

VSick.UnitCityId,

VRequest.Activity,

VRequest.Comment,

VRequest.FactorNumber,

VRequest.IsMultiCartable,

VRequest.MasoolFaniId,

VRequest.NumberInPeriod,

VRequest.RealDeliveryDate,

VRequest.RecommendedDeliveryDate,

VRequest.RequestId,

VRequest.RequestStatusCaption,

VRequest.RequestStatusId,

VRequest.RequestStatusValue,

VRequest.RoleId,

VRequest.StartRequestDate,

VRequest.StatusOfRequestDate,

VRequest.StatusOfRequestId,

VRequest.TotalCash,

VRequest.WarehouseNumber,

VRequestDrug.CountSick,

VRequestDrug.DrugId,

VRequestDrug.DrugName,

VRequestDrug.DrugPrice,

VRequestDrug.TotalCurrent,

VRequestDrug.TotalTakeMedication,

VRequestDrug.ValuePezeshk,

VRequestDrug.ValueRequest

FROM

MMTDB.dbo.QVUnit as VUnit,

MMTDB.dbo.QVUser as VUser,

MMTDB.dbo.QVSick as VSick,

MMTDB.dbo.QVRequest as VRequest,

MMTDB.dbo.QVRequestDrug as VRequestDrug

where

VUnit.UnitCode = VUser.UnitCode

and VUnit.UnitCode = VSick.UnitCode

and VUnit.UnitCode = VRequest.UnitCode

and VRequestDrug.RequestId = VRequest.RequestId ;

STORE Combined into QVD\MMT.qvd(QVD);

Re: join problem

ziadm — Sat, 06 May 2017 06:33:11 GMT

in the debug mode load only limited records and check your final data model if you have synthetic keys or circular loop. If you have that then you data load will never execute and may run out of memory.

Re: join problem

maxgro — Sat, 06 May 2017 10:23:04 GMT

It depends on your SQL database, i.e. data model, tables relations, performance

and on your network for record transfer

Qlik will only get the result of the query and load the in memory db; the only problem could be you have a record with many fields

Try the query with a sql tool (it seems you're on Sql Server, so sql server management studio)

I think you'll get the same problem (million of rows, it takes too long)

topic Re: join problem in QlikView

join problem

Re: join problem

Re: join problem