21 Replies Latest reply: Dec 18, 2017 1:55 PM by Felip Drechsler RSS

    SomaSe - Coluna adicional

    Atila Holtenio

      Pessoal, boa tarde.

       

      Tenho uma duvida, e gostaria da ajuda de vocês. No excel eu consigo fazer isso tranquilamente, mas quando tentei fazer no Qlik Sense, estou com dificuldades

       

      Eu tenho uma tabela com da seguinte maneira

      Item NFPedidoItem PedidoValor
      5100184666/000001/20174500000072
      1
      0
      5100185702/000001/20174500000072
      1
      2550,71
      5100185701/000001/20174500000072
      1
      3772,03

       

      Meu objetivo é criar uma coluna adicional que vai me informar o 'range' de valor que a coluna 'Pedido' se enquadra, por exemplo:

       

      PedidoValorRange
      45000000726.322,74> 5K

       

      Meu maior problema é em conseguir criar a coluna 'Valor' para somar enquanto tiver o Pedido '4500000072', pois sei que a coluna 'Range' é um 'If'

        • Re: SomaSe - Coluna adicional
          Ivan Osatchuk

          Boa tarde,

           

          Não sei se entendi direito, mas segue um exemplo

           

          PEDIDO.png

          • Re: SomaSe - Coluna adicional
            Felip Drechsler

            Olá Atila,

             

            Para o que você quer, ficaria melhor se fosse um IntervalMatch() se entendi direito.

             

            Segue um exemplo abaixo:

             

            Dados:

            Load * Inline

            [

            Item NF, Pedido, Item Pedido, Valor

            5100184666/000001/2017 ,4500000072 ,1,0

            5100185702/000001/2017 ,4500000072 ,1,2550

            5100185701/000001/2017 ,4500000072 ,1,3772

            5100185705/000001/2017 ,4500000072 ,1,9999

            ];

             

            DePara:

            Load * Inline

            [

            De,Para,Desc

            0,4999,Menor que 5k

            5000,10000,Maior que 5k

            ];

             

            IntervalMatch(Valor)

            Left join (Dados)

            Load

            De,

            Para

            Resident DePara;

             

            left join(Dados)

            Load

            De,

            Para,

            Desc

            Resident DePara;

             

            drop fields De,Para from Dados;

            drop table DePara;

             

            Que dá o seguinte:

            Sample.png

              • Re: SomaSe - Coluna adicional
                Atila Holtenio

                Felipe, obrigado de novo pela resposta, mas na verdade ainda não seria exatamente isso que preciso.

                 

                Seguindo exatamente o seu exemplo, o objetivo é que a coluna 'Valor' tenha a soma dos pedidos, entende?

                 

                Na verdade, eu nao sei ao certo se consigo fazer o que preciso.

                 

                Usando o excel, por exemplo, eu faria uma tabela dinamica e colocaria os meus pedidos em uma coluna e valores em outra. Criaria a coluna com os Ranges que gostaria, e teria a tabela. Feito isso, criaria uma tabela adicional para contar <5K ou contar >5K, assim como, somar <5K e somar >5K, pra ter o seguinte resultado final:

                 

                   

                RangeQtde PedidosValor Pedido
                <5K1        6.000,00
                >5K1     100.000,00

                 

                 

                 

                 

                 

                Não sei se estou sendo muito claro, mas estou aqui para eventuais duvidas

                  • Re: SomaSe - Coluna adicional
                    sodre michelly

                    Atila,

                     

                    Acredito que consegue manipulando o script.

                     

                    Fiz um teste, mas como não sei pelo que você pretende agrupar, coloquei por Item NF, mas ai fica a seu critério:

                     

                     

                    TesteComunidade:

                    Load * Inline

                    [

                    Item NF, Pedido, Item Pedido, ValorP

                    5100184666/000001/2017 ,4500000072 ,1,0

                    5100185702/000001/2017 ,4500000072 ,1,2550

                    5100185701/000001/2017 ,4500000072 ,1,3772

                    5100185705/000001/2017 ,4500000072 ,1,9999

                    5100185702/000002/2017 ,4500000072 ,2,2550

                    5100185701/000002/2017 ,4500000072 ,4,3772

                    5100185705/000002/2017 ,4500000072 ,3,9999

                    ];

                     

                     

                    join

                     

                     

                    LOAD

                    [Item NF],

                    sum(ValorP) as Valor_Range,

                    if(sum(ValorP)>5000,'>5k','<5k') as Range

                     

                     

                    Resident TesteComunidade Group by [Item NF]

                     

                     

                    print.JPG

                    • Re: SomaSe - Coluna adicional
                      Felip Drechsler

                      Fazendo a agregação com o exemplo que passei, ficaria algo como o seguinte:

                      Sample.png

                        • Re: SomaSe - Coluna adicional
                          Atila Holtenio

                          Felipe,

                           

                          Depois de algum tempo, consegui fazer como você me falou.

                           

                          Obrigado pela ajuda.

                            • Re: SomaSe - Coluna adicional
                              Felip Drechsler

                              Que bom que te ajudou .

                                • Re: SomaSe - Coluna adicional
                                  Atila Holtenio

                                  Felipe,

                                   

                                  Aproveitando a sua ajuda, finalizei a carga e fiz alguns ajustes, conforme a minha necessidade, porem, tem um unico resultado estranho

                                   

                                  Se puder me ajudar

                                   

                                  Coloquei as classificações:

                                   

                                  DePara1:

                                  Load * Inline

                                  [

                                  De,Para,Desc

                                  0,4999.99,Menor que 5k

                                  5000.00,10000.00,Entre 5K e 10K

                                  10000.01,50000.00,Entre 10K e 50K

                                  50000.01,100000.00,Entre 50K e 100K

                                  100000.01,500000.00,Entre 100K e 500K

                                  500000.01,1000000.00,Entre 500K e 1MM

                                  1000000.01,99999999,Acima de 1MM

                                  ];

                                   

                                  Deu muito certo, porém, um unico pedido nao classificou. Eu achei o pedido, mas nao sei porque ele nao classificou.

                                   

                                  Veja que quando eu coloco o 'Filtro' ele nao aparece o '-'

                                   

                                  Erro_PO.jpg

                                   

                                  Consigo debugar somente essa PO pra tentar entender por que nao classificou corretamente?

                                    • Re: SomaSe - Coluna adicional
                                      Felip Drechsler

                                      Fiz um teste rápido aqui com o seu caso e classifica corretamente (imagem abaixo):

                                      sample.png

                                      Coloca em uma tabela simples, sem agregação (sum([Spend BRL]) pra ver o que sai nesse caso de valor e de descrição por favor.

                                      O seu Sense está com as configurações em inglês no começo do código? Com separador de milhar com vírgula e de decimal como ponto?

                                       

                                      Consegue liberar um aplicativo com dados de exemplo pra ver o que está acontecendo?

                                        • Re: SomaSe - Coluna adicional
                                          Atila Holtenio

                                          Cara, não consigo disponibilizar, mas estou fazendo alguns testes. O meu arquivo de dados tem um outro campo de 'Spend', mas no caso em 'Moeda original'. Quando mudo o codigo para utilizar o campo 'Moeda', o calculo é feito, veja:

                                           

                                          erro2.jpg

                                          Começo do código.

                                          SET ThousandSep=',';

                                          SET DecimalSep='.';

                                           

                                          O mais estranho é que minha base tem 500K linhas, e somente essa ficou sem classificação

                                            • Re: SomaSe - Coluna adicional
                                              Felip Drechsler

                                              Consegue passar a(s) linha(s) original dos seus dados na sua para esse caso em específico?

                                                • Re: SomaSe - Coluna adicional
                                                  Atila Holtenio

                                                  Segue o código. Agora eu tive a certeza de que o problema está, especificamente, neste pedido, pois quando eu troco 'Spend BRL' por 'Spend Moeda', o erro some.

                                                   

                                                  LOAD

                                                      MIR,

                                                      Fornecedor,

                                                      PartNumber,

                                                      Centro,

                                                      GC,

                                                      GM,

                                                      UN,

                                                      "Tipo Doc",

                                                      NumPO,

                                                      ItemPO,

                                                      Moeda,

                                                      Data,

                                                      "Spend BRL",

                                                      Flex,

                                                      "Spend Moeda"

                                                  FROM [lib://AttachedFiles/bd_dashboard_2017.xlsx]

                                                  (ooxml, embedded labels, table is dashboard_2017);

                                                   

                                                    

                                                  DePara1:

                                                  Load * Inline

                                                  [

                                                  De,Para,Desc

                                                  0,4999.99,Menor que 5k

                                                  5000.00,10000.00,Entre 5K e 10K

                                                  10000.01,50000.00,Entre 10K e 50K

                                                  50000.01,100000.00,Entre 50K e 100K

                                                  100000.01,500000.00,Entre 100K e 500K

                                                  500000.01,1000000.00,Entre 500K e 1MM

                                                  1000000.01,99999999,Acima de 1MM

                                                  ];

                                                   

                                                   

                                                  IntervalMatch("Spend BRL")

                                                  Left join (dashboard_2017)

                                                  Load

                                                  De,

                                                  Para

                                                  Resident DePara1;

                                                   

                                                  left join(dashboard_2017)

                                                  Load

                                                  De,

                                                  Para,

                                                  Desc

                                                  Resident DePara1;

                                                   

                                                  drop fields De,Para from dashboard_2017;

                                                  drop table DePara1;

                                                    • Re: SomaSe - Coluna adicional
                                                      Felip Drechsler

                                                      Oi Atila, quis dizer os dados na sua base, as vezes pode acontecer de não ter esse valor para esse dado e por isso ele não está classificando com o Intevalmatch.

                                                       

                                                      Imagino que possa estar nulo / vazio o valor e quando ele for tentar usar a função, não acha valor nenhum.

                                                       

                                                      Tente fazer o seguinte para ver se esse pedido vem classificado:

                                                       

                                                      LOAD

                                                          MIR,

                                                          Fornecedor,

                                                          PartNumber,

                                                          Centro,

                                                          GC,

                                                          GM,

                                                          UN,

                                                          "Tipo Doc",

                                                          NumPO,

                                                          ItemPO,

                                                          Moeda,

                                                          Data,

                                                          Alt("Spend BRL",0) as "Spend BRL",

                                                          Flex,

                                                          Alt("Spend Moeda",0) as "Spend Moeda",

                                                      FROM [lib://AttachedFiles/bd_dashboard_2017.xlsx]

                                                      (ooxml, embedded labels, table is dashboard_2017);

                                                        • Re: SomaSe - Coluna adicional
                                                          Atila Holtenio

                                                          Felipe,

                                                           

                                                          Gerei um arquivo de dados apenas com duas linhas de POs, uma com o exemplo que nao está correto, e um outro PO.

                                                           

                                                          No meu aqui, continua com erro nesta linha, e veja que a outra não está considerando o mesmo que vc colocou, pois o resultado da soma das POs deveria ser >5K, correto?

                                                           

                                                          Tabela com os dados está anexa

                                                           

                                                          Resultado

                                                           

                                                          erro3.jpg

                                                            • Re: SomaSe - Coluna adicional
                                                              Felip Drechsler

                                                              Oi Atila,

                                                               

                                                              Descobri o problema e está acontecendo quando estão sendo importandos os seus dados para o Qlik.

                                                              É alguma questão de arredondamento que não está conseguindo interpretar o valor direito para o pedido

                                                              4500086885

                                                               

                                                              No arquivo excel que você passou, o valor "Spend BRL" está como 988,71875 e se você alterar ele para 988,71 ele consegue fazer o cálculo corretamente.

                                                               

                                                              sample.png

                                                               

                                                              Quando for fazer a carga dos dados, faça o seguinte (utilizei o arquivo que você passou no caso como origem de dados):

                                                               

                                                              Dados:

                                                              LOAD MIR,

                                                                   Forn,

                                                                   PN,

                                                                   Base,

                                                                   GC,

                                                                   GM,

                                                                   UM,

                                                                   [Tipo Doc],

                                                                   NumPO,

                                                                   ItemPO,

                                                                   Moeda,

                                                                   Data,

                                                                  round([Spend BRL],0.01) as [Spend BRL],

                                                                   Flex,

                                                                   [Spend Moeda]

                                                              FROM

                                                              [C:\Users\felipe.lourenco\Desktop\teste.xlsx]

                                                              (ooxml, embedded labels, table is Plan1);

                                                               

                                                              Isso irá arredondar o valor para o valor mais próximo na segunda casa decimal e assim irá conseguir fazer Intervalmatch.

                                                               

                                                              Felipe.

                                                                • Re: SomaSe - Coluna adicional
                                                                  Atila Holtenio

                                                                  Deu certo!!! Obrigado.

                                                                   

                                                                  Agora que eu coloquei a base toda, e coloquei a ajuda inicial que você tinha me falado, eu vi que nao deu muito certo.

                                                                   

                                                                  Usando essa mesma tabela que te passei como exemplo, o resultado do PO 4500000072 deveria ser >5K, correto?

                                                                   

                                                                  Visto que a soma das linhas da PO são maiores que 5K.

                                                                    • Re: SomaSe - Coluna adicional
                                                                      Felip Drechsler

                                                                      O intervalmatch está sendo usado para linhas individuais Atila, então cada linha está sendo avaliada conforme o DePara e o campo "Spend BRL".

                                                                       

                                                                      A agregação (soma, média, etc) não vai dar certo com esse esquema que está ai.

                                                                      O print ao meu ver está correto por valor de "Spend BRL" mas você pode estar querendo outra lógica.

                                                                        • Re: SomaSe - Coluna adicional
                                                                          Atila Holtenio

                                                                          Eu peço desculpas, porque eu 'mudei' o objetivo principal do tópico que criei.

                                                                           

                                                                          A lógica seria usar o intervalmatch para a coluna pedido, mas talvez nao seja possivel da forma que eu quero, pois estou considerando o que eu faria no excel.

                                                                           

                                                                          De qualquer maneira, obrigado pela ajuda

                                                                            • Re: SomaSe - Coluna adicional
                                                                              Felip Drechsler

                                                                              Com o codigo abaixo da pra chegar no que imagino que você queira Atila:

                                                                              Então tem-se a classificação individual (linha a linha) e a soma do valor "Spend BRL" por NumPO.

                                                                              sample.png

                                                                               

                                                                              Dados:

                                                                              LOAD MIR,

                                                                                  Forn,

                                                                                  PN,

                                                                                  Base,

                                                                                  GC,

                                                                                  GM,

                                                                                  UM,

                                                                                  [Tipo Doc],

                                                                                  NumPO,

                                                                                  ItemPO,

                                                                                  Moeda,

                                                                                  Data,

                                                                                  round([Spend BRL],0.01) as [Spend BRL],

                                                                                  Flex,

                                                                                  [Spend Moeda]

                                                                              FROM

                                                                              [C:\Users\felipe.lourenco\Desktop\teste.xlsx]

                                                                              (ooxml, embedded labels, table is Plan1);

                                                                               

                                                                              soma:

                                                                              Load

                                                                              NumPO,

                                                                              sum(round([Spend BRL],0.01)) as [Soma Spend BRL],

                                                                              count(NumPO) as [Contador Spend BRL]

                                                                              Resident Dados

                                                                              group by NumPO;

                                                                               

                                                                              DePara:

                                                                              Load * Inline

                                                                              [

                                                                              De,Para,Desc

                                                                              0,4999,Menor que 5k

                                                                              5000,10000,Entre 5K e 10K

                                                                              10001,50000,Entre 10K e 50K

                                                                              50001,100000,Entre 50K e 100K

                                                                              100001,500000,Entre 100K e 500K

                                                                              500001,1000000,Entre 500K e 1MM

                                                                              1000001,99999999,Acima de 1MM

                                                                              ];

                                                                               

                                                                              IntervalMatch([Spend BRL])

                                                                              Left join (Dados)

                                                                              Load

                                                                              De,

                                                                              Para

                                                                              Resident DePara;

                                                                               

                                                                              left join(Dados)

                                                                              Load

                                                                              De,

                                                                              Para,

                                                                              Desc as DescIndividual

                                                                              Resident DePara;

                                                                               

                                                                              drop fields De,Para from Dados;

                                                                               

                                                                              IntervalMatch([Soma Spend BRL])

                                                                              left join (soma)

                                                                              Load

                                                                              De,

                                                                              Para

                                                                              Resident DePara;

                                                                               

                                                                              left join(soma)

                                                                              Load

                                                                              De,

                                                                              Para,

                                                                              Desc as DescSoma

                                                                              Resident DePara;

                                                                               

                                                                              left join (Dados)

                                                                              Load

                                                                              NumPO,

                                                                              DescSoma

                                                                              Resident soma;

                                                                               

                                                                              drop fields De,Para from Dados;

                                                                               

                                                                              drop tables soma,DePara;