read json Field from QVD Table

[hiela001]

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) ? 

[MayilVahanan]

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;

CombinedGenericTable:
Load distinct ID1&'|'&Name&'|'&creatDate as Key, * Inline
[
ID1 | Name | creatDate | Propereties

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:

[hiela001]

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 ? 

[hiela001]

any other Helps ?? 

[MayilVahanan]

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:

......

[hiela001]

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;

[MayilVahanan]

Hi @hiela001 

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

[hiela001]

Hi Mayil Vahanan , 

this is my Example with QVD table and QVW code

[MayilVahanan]

HI @hiela001 

File is working fine .

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

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