Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Existing Table 1 -  "dimensionTable"

nameColumn typedataType
ga:landingPageDIMENSIONSTRING
ga:sessionsMETRICINTEGER
ga:pageViewsMETRICINTEGER

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:landingPagega:sessionsga:pageViews
/home13
/home/login25
/home/myAccount26
/search623
/contactUs422
1 Solution

Accepted Solutions
thomaslg_wq
Creator III
Creator III

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;

View solution in original post

3 Replies
thomaslg_wq
Creator III
Creator III

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;

thomaslg_wq
Creator III
Creator III

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;

Not applicable
Author

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