Histórico

Com o advento de bancos de dados não relacionais orientados a documentos (MongoDB e outros) e API´s para integração entre sistemas frequentemente em Qlik somos desafiados a tratar campos em formato JSON (para entender o que é este formato leia aqui)

 

Desafio

Em Qlik temos que tratar campos com informações como essa (coletada do Cartola)

url (tente abrir no seu browser) https://api.cartolafc.globo.com/partidas

Capturar.PNG

 

Fazendo essa estrutura ficar um pouco mais "amigável" para a leitura

Capturar.PNG

 

ou seja, o conteúdo do campo é uma string que representa uma série de tabelas aninhadas que não correspondem ao modelo tabular que conhecemos (Excel, CSV, QVD, SQL, etc).

 

So what?

Felizmente os conectores da Qlik (REST, MongoDB, etc) tratam essa complexidade convertendo o aninhamento de tabelas em uma série de tabelas "normais"

Mas e se você não tiver acesso a elas? E se precisar buscar isso em um arquivo texto, ou essa informação estiver em uma coluna de um banco e você precisar tratar essa informação?

As funções de string comuns do Qlik (Subfield, textbetween, mid, left, etc) são muito "capengas" para tratar esse tipo de informação

 

Uma Solução

Existe um parâmetro do comando LOAD (help) que é uma possível saída. Ele é muito pouco conhecido, quase um patinho feio. É o from_field (verifique abaixo a definição de sintaxe do LOAD)

 

LOAD [ distinct ] fieldlist
[(
from file [ format-spec ] | 
from_field fieldassource [format-spec]|
inline data [ format-spec ] |
resident table-label | autogenerate size ) |
extension pluginname.functionname([script] tabledescription
)]
[where criterion | while criterion ]
[group by groupbyfieldlist ]
[order by orderbyfieldlist ]

 

O que esse cara faz?

Ele busca os valores de um campo previamente carregado (lembra do Resident? Parecido, mas não é a mesma coisa). Ele tem um parâmetro (format-spec) que é justamente fazer o parse de JSON!!

 

Como usar?

Exemplo simples usando para tratar os dados do Cartola

1 - Crie uma conexão simples do tipo WebFile apontando para https://api.cartolafc.globo.com/partidas

Ou carregue qualquer JSON que você tiver a mão

 

json:
LOAD
@1 AS json_field
FROM [lib://Cartola (qlikcloud_qlikid_cuv)]
(
  txt,
  utf8,
  no labels,
  no eof,
  delimiter is '\t',
  URL is "https://api.cartolafc.globo.com/partidas"
);

 

Teremos uma tabela com 1 linha que tem uma string enorme com todos os dados de partidas,clubes etc.

Analisando-se a estrutura (recomendo usar o https://jsonformatter.curiousconcept.com/ para investigar a estrutura do documento) podemos ver que existe uma tabela "partidas/clubes" que armazena vários documentos (cada clube) e cada clube possui id, nome, etc e essas informações ficam na tabela "partidas/clubes/{id do clube}"

Capturar.PNG

Vamos agora converter aquela tabela de uma 1 linha com uma string para uma tabela com 1 linha e "n" colunas (uma com o id interno de cada um dos documentos)

 

t:load * from_field(json,json_field) (json, Table is "partidas/clubes");

fazendo assim o Qlik irá gerar uma tabela com os id internos para cada um dos documentos

Capturar.PNG

Agora vamos buscar na nossa base, todos os clubes, passando o seu id para recuperar as informações da tabela

 

for j = 1 to NoOfFields('t')
  let clube = FieldName(j,'t');
  if IsNum(clube) then // Tratando porque o Qlik armazena o nome da tabela como uma das colunas
    Clubes:
    Load
      $(clube) as key,
      id as clube_id,
      nome,
      abreviacao
    from_field(json,json_field) (json, Table is "partidas/clubes/$(clube)");
  endif
next

 

Fazendo isso, chegamos ao que queríamos

Capturar.PNG

 

 

Se alguém conseguir otimizar o código, por favor compartilhe

 

Have fun, be Qlik, we love data!