Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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;
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