Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

multi valued field representation

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

idnameuser_idlabels
"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

1 Solution

Accepted Solutions
rbecher
MVP
MVP

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

TweetLabels.PNG

Astrato.io Head of R&D

View solution in original post

10 Replies
marcus_sommer

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

Not applicable
Author

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 ,,

idnameuser_idlabels
"648435107775119360"A2RZOLW39224569Complaint,Idea,Request
"650217828784730112"Simitha T.Singam1006572192SalesLead,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

rbecher
MVP
MVP

I would rather use an associated table and then use CONCAT() to aggregate all labels per id..

- Ralf

Astrato.io Head of R&D
marcus_sommer

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

rbecher
MVP
MVP

..if crouch74‌ uploads a data sample I can do.

Astrato.io Head of R&D
Not applicable
Author

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

rbecher
MVP
MVP

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

TweetLabels.PNG

Astrato.io Head of R&D
rbecher
MVP
MVP

Btw. you should load tweet_id as text(tweet_id)..

Astrato.io Head of R&D
Not applicable
Author

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 ?