Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to represent a multi valued field in qlik sense
let's say I have the following structure of data
{
"id":"648435107775119360",
"name":"Ali",
"user_id" : "39224569"
"labels" : [1,2,3]
}
and I have a lookup for labels as an inline table that will be used for mapping
I want to map the object above to be something like
id | name | user_id | labels |
---|---|---|---|
"648435107775119360" | "Ali" | "39224569" | "Complaint,Question,Sales" |
so that I can use the labels field as a dimension,
can I make a multi valued field in the table ,, or should I create something like an associative table between the main table and the labels table ?
It would be nice if you could provide a load script sample ??
thanks
Just create another linked table and load it with SubField():
LabelMap:
Mapping LOAD * INLINE [
F1,F2
1,Question
2,Spam
3,Sales Lead
4,Idea
];
Sheet1:
LOAD
tenant_id,
country,
dialect,
query_id,
tweet_id,
latitude,
created_at,
retweet_count,
negative,
screen_name,
favorite_count,
id,
"text",
lang,
longitude,
has_geo,
neutral,
profile_image_url,
videos_url,
positive,
label,
search_date,
unresolved,
country_code,
photo_count,
user_id,
_version_,
datasource,
name,
video_count,
twitter_lang,
klout_score,
spam,
photos_url
FROM [lib://Daten/Sheet1.qvd]
(qvd);
TweetLabels:
LOAD
tweet_id,
ApplyMap('LabelMap', subfield(label, ','), 'Unkown') as TweetLabel
FROM [lib://Daten/Sheet1.qvd]
(qvd);
With something like this:
subfield(YourField, ':', 1) as Category,
subfield(YourField, ':', 2) as Value
could you split the values and with a The Generic Load could you transform these stream into a table.
- Marcus
Dear Marcus,
Thanks for your answer
I want to maintain the labels field as a multi valued list or as an array
as the row can be labeled with 20 labels ,,
id | name | user_id | labels |
---|---|---|---|
"648435107775119360" | A2RZOLW | 39224569 | Complaint,Idea,Request |
"650217828784730112" | Simitha T.Singam | 1006572192 | SalesLead,Offensive,Request |
I want these labels to be dynamic ,, so does qlik sense has a multi valued fields ??
I want to make a filter list with all the available labels in a sheet ,, I also want to use the labels as dimensions ,, so I can display a pie chart with the share of each label ,,
can the generic load help me with that ,, cause I can't understand how can it help me in this situation
appreciate your effort
I would rather use an associated table and then use CONCAT() to aggregate all labels per id..
- Ralf
In general worked the generic-approach. Here directly adapted (unless the key-generation - which might the more difficult part in your real scenario if the data are quite heterogeneous) from the example from HIC:
RawData:
load
purgechar(subfield(Data, ':', 1), '"') as Attribute,
purgechar(subfield(Data, ':', 2), '"') as Value
Inline [
Data
"id":"648435107775119360"
"name":"Ali"
"user_id" : "39224569"
"labels" : ["1,2,3"]]
"id":"111111"
"name":"AliBaba"
"user_id" : "55555"
"labels" : ["1,3,5"]]
]; // second data-part is only to check the logic with more then one record
CreateKey:
load Attribute, Value, ceil(rowno() / fieldvaluecount('Attribute')) as Key Resident RawData;
Generic:
generic load Key, Attribute, Value resident CreateKey;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='Generic' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable:
Load distinct Key Resident CreateKey;
drop tables RawData, CreateKey;
// trace '$(vListOfTables)';
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
Maybe the suggestion from rbecher is easier: could you elaborate some more details?
- Marcus
..if crouch74 uploads a data sample I can do.
Dears, Thanks for your efforts
I've attached an app with sample of the data
please notice the "label" field ,, it has different values for each tweet
I want at first to map these ids to names so that
Id -> Name
------------------
1 -> Question
2 -> Spam
3 -> Sales Lead
4 -> Idea
I want to be able to provide the user with a bar chart for example ,,
Question : 50%
Spam : 50%
Sales Lead : 30%
Idea : 20%
How can I accomplish something like this
appreciate your efforts
Just create another linked table and load it with SubField():
LabelMap:
Mapping LOAD * INLINE [
F1,F2
1,Question
2,Spam
3,Sales Lead
4,Idea
];
Sheet1:
LOAD
tenant_id,
country,
dialect,
query_id,
tweet_id,
latitude,
created_at,
retweet_count,
negative,
screen_name,
favorite_count,
id,
"text",
lang,
longitude,
has_geo,
neutral,
profile_image_url,
videos_url,
positive,
label,
search_date,
unresolved,
country_code,
photo_count,
user_id,
_version_,
datasource,
name,
video_count,
twitter_lang,
klout_score,
spam,
photos_url
FROM [lib://Daten/Sheet1.qvd]
(qvd);
TweetLabels:
LOAD
tweet_id,
ApplyMap('LabelMap', subfield(label, ','), 'Unkown') as TweetLabel
FROM [lib://Daten/Sheet1.qvd]
(qvd);
Btw. you should load tweet_id as text(tweet_id)..
That's exactly what I need ,, Thank you very much you have been so helpful
but if it's a larger amount of data and it will be loaded through the restful connector ,, should I do this twice ?? can't I just depend on the previously loaded data ?