Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: multi valued field representation

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

10 Replies

Re: multi valued field representation

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

Re: multi valued field representation

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

MVP
MVP

Re: multi valued field representation

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

- Ralf

Re: multi valued field representation

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

MVP
MVP

Re: multi valued field representation

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

Not applicable

Re: multi valued field representation

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

MVP
MVP

Re: multi valued field representation

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

MVP
MVP

Re: multi valued field representation

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

Not applicable

Re: multi valued field representation

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 ?