0 Replies Latest reply: Jun 2, 2017 2:22 PM by Andre Oliveira RSS

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

    Andre Oliveira

      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!