11 Replies Latest reply: May 29, 2016 5:54 AM by Ahmed Eid RSS

    multi valued field representation

    Ahmed Eid

      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

        • Re: multi valued field representation
          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

            • Re: multi valued field representation
              Ahmed Eid

              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

                • Re: multi valued field representation
                  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 Ralf is easier: could you elaborate some more details?

                   

                  - Marcus

                    • Re: multi valued field representation
                      Ralf Becher

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

                        • Re: multi valued field representation
                          Ahmed Eid

                          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

                            • Re: multi valued field representation
                              Ralf Becher

                              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

                    • Re: multi valued field representation
                      Ralf Becher

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

                       

                      - Ralf