topic Re: How to make new table with fields name coming from rows of an existing table? in Integration, Extension & APIs

How to make new table with fields name coming from rows of an existing table?

Thu, 24 Nov 2016 11:22:35 GMT

Existing Table 1 - "dimensionTable"

name	Column type	dataType
ga:landingPage	DIMENSION	STRING
ga:sessions	METRIC	INTEGER
ga:pageViews	METRIC	INTEGER

Existing Table 2 - "dimensionValue"

data
/home
1
3
/home/login
2
5
/home/myAccount
2
6
/search
6
23
/contactUs
4
22

How can I achieve the below mentioned table using above tables with the help of data load editor ?
I got the above mentioned data from the google analytics API in JSON and the QLIK Rest connector has created these above mentioned tables.

Desired Table - "MasterTable"

ga:landingPage	ga:sessions	ga:pageViews
/home	1	3
/home/login	2	5
/home/myAccount	2	6
/search	6	23
/contactUs	4	22

Re: How to make new table with fields name coming from rows of an existing table?

thomaslg_wq — Thu, 24 Nov 2016 14:53:57 GMT

Here you go, free of charge:

// here your first table

Fields:

LOAD

name

FROM [lib://Test/Tests_Community.xlsx]

(ooxml, embedded labels, table is Parameter);

// here your second table

Data:

LOAD

data,

recno() as Line

FROM [lib://Test/Tests_Community.xlsx]

(ooxml, embedded labels, table is Values);

for i=0 to noofrows('Fields')-1

let vFieldName=peek('name',i,'Fields');

[Data_$(vFieldName)]:

LOAD *,

recno() as LineNumber;

LOAD

data as [$(vFieldName)]

resident Data

where floor((Line-($(i)+1))/noofrows('Fields'))=(Line-($(i)+1))/noofrows('Fields');

next i

for i=0 to noofrows('Fields')-1

let vFieldName=peek('name',i,'Fields');

if $(i)=0 then

let vAppelTable='noconcatenate FinalTable:';

else

let vAppelTable='left join(FinalTable)';

endif

$(vAppelTable)

LOAD *

resident [Data_$(vFieldName)];

Drop table [Data_$(vFieldName)];

next i

Drop tables Fields, Data;

Drop field LineNumber;

Re: How to make new table with fields name coming from rows of an existing table?

thomaslg_wq — Thu, 24 Nov 2016 15:12:14 GMT

Even better with only this:

Fields:

LOAD

name

FROM [lib://Test/Tests_Community.xlsx]

(ooxml, embedded labels, table is Parameter);

Data:

LOAD

data,

recno() as Line

FROM [lib://Test/Tests_Community.xlsx]

(ooxml, embedded labels, table is Values);

for i=0 to noofrows('Fields')-1

let vFieldName=peek('name',i,'Fields');

if $(i)=0 then

let vAppelTable='noconcatenate FinalTable:';

else

let vAppelTable='left join(FinalTable)';

endif

$(vAppelTable)

LOAD *;

LOAD *,

recno() as LineNumber;

LOAD

data as [$(vFieldName)]

resident Data

where floor((Line-($(i)+1))/noofrows('Fields'))=(Line-($(i)+1))/noofrows('Fields');

next i

Drop tables Fields, Data;

Drop field LineNumber;

Re: How to make new table with fields name coming from rows of an existing table?

Thu, 24 Nov 2016 16:01:07 GMT

Exactly this is what I wanted !!!!!!!!!!! and so fast !!

Although I am still not able to understand the code.

I will try to understand it tomorrow again.

Now I can leave my office and go home