8 Replies Latest reply: Aug 3, 2017 2:22 AM by Max Ålander RSS

    Reformatting data in the data load editor

    Max Ålander

      Hi,

       

      I'm using Qlik Sense REST connector to import data from Mixpanel using Mixpanel Export API. The data that I import is a JSON, and this specific one I find very difficult to work with. I've tried several different ways but I can't get it right. Here below I show you some the relevant columns from the relevant tables in the data set, and in what format I want the data in.

       

      I have several tables for each date of interest, where the number of tables most likely will grow to a couple of hundred when looking at a longer time period. The number of tables is unknown when importing the data so it would be preferable for the script to handle an arbitrary number of tables. My goal is then to be able to visualise this data in Qlik Sense, and as I understand it the output is in a format that I can work with.

       

      Looking at the names of the tables, the first one is called simply table. Each one following this has an appended string where the first one is table_u0, followed by table_u1, table_u2 and so on.

       

      How do I create the wanted output from the tables I start with?

       

      table:

      eventcount__KEY_2017-06-02
      event12501
      event22101
      event32001
      event41801

       

       

      table_u0:

      event_u0count_u0__KEY_2017-06-09
      event1

      340

      1
      event23001
      event32801
      event42501

       

      output:

      dateevent1event2event3event4
      2017-06-02250210200180
      2017-06-09340300280250

       

      I've successfully used generic load to be able to turn around tables table and table_u0, but not been able to connect and include the date in the format I want to.

       

       

       

       

      Note! In this post I've tried to simplify the date to make it easier to explain and understand. If it's necessary I can attach the complete format of my imported data set to paint a more complete picture.

       

      Also, there is a separate table of dates but it does not seem to be directly connected to the other tables by any keys.

       

      dates:

      @Value__KEY_meta
      2017-06-021
      2017-06-091