multi valued field representation

[#1]

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

[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

[#3]

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

[rbecher]

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

- Ralf

[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]

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

[#7]

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 · Accepted solution]

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

[rbecher]

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

[#10]

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 ?