Tratando tabela Json web sem plugin

messiasstelzer — Wed, 21 Sep 2022 17:07:35 GMT

Ola!!

Estou disponibilizando um trabalho que fiz para ajudar outras pessoas que assim como não conseguiram instalar o plugin do Json para Qlikview.

I hope I can solve a lot of people's problem who work loading JSON data.

//////////////////////////////// versão 1.0 ///////////////////////////////////////
//////////////////////////////// SET - 2019 ///////////////////////////////////////

TRACE *****************************************************************************************************;
TRACE **************************** Leitura do Base de dados ***********************************;
TRACE *****************************************************************************************************;

Dado:
LOAD *
FROM
[https://api.cartolafc.globo.com/partidas]
(txt, utf8, no labels, delimiter is '\t', msq);

1:
LOAD Replace(Replace(Replace(Replace(Replace(SubField(@1,'],"clubes"',1),'""','"N/D"'),',"',';"'),'"}},{','"}}|{'), '{"partidas":[',''),'":','"&') as Partida,
// SubField(@1,'}}],',1) as Partida,
Replace(Replace(Replace(SubField(@1,'],"clubes":{',2),'"},"2','"}&"2'),'"},"3','"}&"3'),':','|') as Clube
// Trim(TextBetween(@1,'"Clube":[',']')) as Clube
// Replace(Replace(Replace(Text,'","','";"'),'":"','"|"'), ':null,','|"N/D";') as CallRecord
Resident Dado;

TRACE *****************************************************************************************************;
TRACE **************************** Tratamento da Base de dados ***********************************;
TRACE *****************************************************************************************************;

Partida_Origem:
LOAD SubField( Partida,'|', IterNo()) as Text_P,
IterNo() as IDp
Resident 1
While iterno() <= RangeMax(SubStringCount(Partida,'|'))+1;

DROP Field Partida From Partida_Origem;

Clube_Origem:
LOAD SubField(Clube,'&', IterNo()) as Text_C,
IterNo() as IDc
Resident 1
While iterno() <= RangeMax(SubStringCount(Clube,'&'))+1;

DROP Field Clube From Clube_Origem;
DROP Table Dado, 1;

TRACE *****************************************************************************************************;
TRACE **************************** Tratamento dos campos ***********************************;
TRACE *****************************************************************************************************;

article Tratando tabela Json web sem plugin in Member Articles

Tratando tabela Json web sem plugin