Join from a QVD file with a Where Clause from specific field

[#1]

Hi guys, how u doing?

Firstly I'm  from Brazil and forgive my English errors!

So this is the deal:

- I have a bunch of databases to select from tables of Salesforce Connect I put all my SQL/MYSQL knowledge but stuck in little situation.

I have SELECTED a base called: tblOS (contain a few columns and a lot of information like 200k of registers and will be incremented every week). So to prevent a lot of hours waiting I create a QVD file. In this table, I have some primary keys, one of them is called as: CASOID

So, I trying to make a LEFT JOIN from tblCase INTO QVD file (tblOS) to get the number of the case to every line in tblOS. But this tblCase is from Salesforce, and there I have a lot but a lot of Cases and to optimize I think if we could set a where clause into tblCase select, where Case.ID = tblOS.CasoID (CasoId is a field from QVD file).

It's possible to select a table from Salesforce (external database) limited to the specific quantity of registers in QVD? cause otherwise I'll need set in CASE table, the clause by Created Data and will result in more lines that I need (like a 360k for 260k of service orders) and will take a lot of time, so if I already have all I need in my QVD file why not load only tblCASE for each CaseId I have in my qvd improving the time of the query? How can I optimize this query?

my code is above!

Set all ID in RED

tblOS:

  Load

        Service_Order__c.AVX_Atendente__c AS Equipe,

        Service_Order__c.CreatedById AS CriadoPorId,

        Service_Order__c.OwnerId AS ProprietarioId,

        Service_Order__c.AN8_Fornecedor__c AS AN8Fornecedor,

        Service_Order__c.Partner_Account__c AS AutorizadoId,

        Service_Order__c.Consultor_de_servi_os__c AS Consultor,

        Service_Order__c.Order_Type__c AS Tipo,

        Service_Order__c.Case__c AS CasoId,

        Service_Order__c.Name AS NumOS,

        Service_Order__c.State__c AS OSEstado,

        Service_Order__c.City__c AS OSCidade,

        Service_Order__c.Bairro__c AS OSBairro,

        Service_Order__c.Zip__c AS OSCEP,

        Service_Order__c.Order_Status__c AS StatusOS,

        Service_Order__c.Defeito_Reclamado__c AS DefeitoReclamado,

        Service_Order__c.Linha_de_Produto_trd__c AS LinhaProduto,

        Service_Order__c.Produto_Instalado__c AS ModeloUsual,

        Service_Order__c.CreatedDate AS DataCriacao,

        Service_Order__c.diasUteisCriacao_PrimeiraVisita__c AS DiasUteisPrimeiraVisit,

        Service_Order__c.diasUteisCriacao_Agendamento__c AS DiasUteisAgendamento,

        Service_Order__c.Data_do_Agendamento__c AS DataPrimeiroAgendamento,

        Service_Order__c.First_Queued_DateTime__c AS DataAgendadoPara,

        Service_Order__c.Data_do_Encerramento__c AS DataEncerramento,

        Service_Order__c.Codigo_Defeito_QES__c AS FaultCode,

        Service_Order__c.SLA_Ordem_de_Servico__c AS SLA,

        Service_Order__c.Processamento_SPV_concluido__c AS ProcSPV,

        Service_Order__c.Possivel_SPV__c AS PossivelSPV,

        Service_Order__c.SPV__c AS SPV;

        .. ... ...

Store tblOS into Path\Qlik\OS.qvd;

after a QVD file is saved in my location, I execute this code above:

tblQVD:

LOAD *

FROM [lib://tblAllOS/OS.qvd]

(qvd);

Left Join (tblQVD)

  Load Account.Id As AutorizadoId,

        Account.Name as NomeConta;          

  SELECT

       Id,

       Name    

  FROM Account

  WHERE

      Account.Status__c = 'Ativo' And

      Account.Type = 'RNSA' And

      Account.Tipo_de_Prestador__c in ('WG') And

      Account.Data_do_Descredenciamento__c = Null;

Left Join (tblQVD)

  Load

      User.Id as CriadoPorId,

      User.Name as NomeSalesforce,

      User.Username as NomeUsuario;

    SELECT

      Id,

      Name,

      Username

    FROM

      User;

Left Join (tblQVD)  

  Load

      User.Id as ProprietarioId,

      User.Name as Proprietario;

    SELECT

      Id,

      Name

    FROM

      User;

Left Join (tblQVD)

  Load

    Case.Id as CasoId,

        Case.CaseNumber as Caso;

      

    Select

         Id,

        CaseNumber

    FROM

         Case

    WHERE

         Case.Id = tblQVD.CasoId;

Store tblOS into C:\Users\reisand01\Documents\Qlik\OS.qvd

Tks! cya!