topic Re: read json Field from QVD Table in QlikView

read json Field from QVD Table

hiela001 — Sat, 16 Nov 2024 17:48:42 GMT

i stored many Fields from a shopDB in qvd Tabel, the Problem now is i have a Field that is in Json Format , the other Fields are normal Fields : for Expmple

ID | Name | creatDate | Propereties

12356| Laptop|01.01.2015| {"height":"30","Width":"233","length":"24"}

does somebody have any Idea how to get 3 Fields from the Field Propereties( height-Width and length) ?

Re: read json Field from QVD Table

MayilVahanan — Thu, 19 Nov 2020 07:57:43 GMT

Hi @hiela001

Try like below

Temp:
Load *,ID1&'|'&Name&'|'&creatDate as Key, SubField(T,':', 1) as Column, SubField(T,':',2) as Value;
Load *, SubField(PurgeChar(Propereties,'{}"'), ',') as T Inline
[
ID1 | Name | creatDate | Propereties

12356| Laptop|01.01.2015| {"height":"30","Width":"233","length":"24"}
](delimiter is '|');

GenericDB:
Generic LOAD Key, Column, Value Resident Temp;

12356| Laptop|01.01.2015| {"height":"30","Width":"233","length":"24"}
](delimiter is '|');

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'GenericDB.*') THEN

LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i;

DROP Table Temp;

Output:

Re: read json Field from QVD Table

hiela001 — Thu, 19 Nov 2020 11:09:06 GMT

Hi Mayil

thank you for your help it seems to be a very good Solution.

i'm a Qlik beginner , how should the Code be if i want to get the Data from QVD and not from Inline table like this Exapmle ? what i have to change ?

Re: read json Field from QVD Table

hiela001 — Thu, 26 Nov 2020 12:19:49 GMT

any other Helps ??

Re: read json Field from QVD Table

MayilVahanan — Thu, 26 Nov 2020 12:54:52 GMT

Hi @hiela001

Instead of inline table , u can use replace with ur qvd like below

Load *,ID1&'|'&Name&'|'&creatDate as Key, SubField(T,':', 1) as Column, SubField(T,':',2) as Value;

Load *, SubField(PurgeChar(Propereties,'{}"'), ',') as T

From urqvd(qvd);

GenericDB:

......

Re: read json Field from QVD Table

hiela001 — Thu, 26 Nov 2020 16:08:26 GMT

Hi MayilVahanan,

i change the code to this one bellow, but it didn't work , there is a problem with the GenericDB Table

:

________________

Temp:
Load *,id & Name &creatDate as Key, SubField(T,':', 1) as Column, SubField(T,':',2) as Value;

Load *, SubField(PurgeChar(Propereties,'{}"'), ',') as T

FROM
[C:\Desktop\Example.qvd]
(qvd);

GenericDB:
Generic LOAD Key, Column, Value Resident Temp;

CombinedGenericTable:
Load distinct id & Name &creatDat as Key, *

FROM
[C:\Desktop\example.qvd]
(qvd);

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'GenericDB.*') THEN

LEFT JOIN ([CombinedGenericTable]) LOAD distinct * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i;

DROP Table Temp;

Re: read json Field from QVD Table

MayilVahanan — Fri, 27 Nov 2020 01:01:11 GMT

Hi @hiela001

is it possible to provide the example qvd ? to verify.

Re: read json Field from QVD Table

hiela001 — Fri, 27 Nov 2020 11:41:54 GMT

Hi Mayil Vahanan ,

this is my Example with QVD table and QVW code

Re: read json Field from QVD Table

MayilVahanan — Sat, 28 Nov 2020 01:10:55 GMT

HI @hiela001

File is working fine .

If you've space in tables, then use like this

IF WildMatch('[$(vTable)]', 'GenericDB.*') THEN