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: 
Anonymous
Not applicable

Reformatting data in the data load editor

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
1 Solution

Accepted Solutions
jmvilaplanap
Specialist
Specialist

Hi

I think could you put the name of the field in a variable and replicate it in all the rows, then you will have a table like this

event1, 250, 2017-06-02

event2, 210, 2017-06-02

etc...

And later concatenate all tables

(I think will be better than the output table)

Regards

View solution in original post

8 Replies
jmvilaplanap
Specialist
Specialist

Hi

I think could you put the name of the field in a variable and replicate it in all the rows, then you will have a table like this

event1, 250, 2017-06-02

event2, 210, 2017-06-02

etc...

And later concatenate all tables

(I think will be better than the output table)

Regards

Anonymous
Not applicable
Author

Hi Jose,

That sounds like an interesting idea! I still feel like I need a little bit more guidance, how would you do that? Or maybe there is somewhere I can read up on this?

Best

jmvilaplanap
Specialist
Specialist

Hi,

Try with the FieldName function

https://community.qlikview.com/thread/63462

And store it in a variable. Maybe will bes necessary load twice each table, one for get the field name (you can read only one row) and other for load the data (with the new values from the variable)

Anonymous
Not applicable
Author

Hi,

that's great! I've seen that function before and it'll probably do the trick.

I have bigger issues understanding how to load the table to insert the variables (containing the field name) in the correct column. It would be great if you could give an example of how that is done, and also specifically how to insert it in all four rows.

Thanks!

jmvilaplanap
Specialist
Specialist

Hi

You can try this

FieldNames:

First 1 LOAD * FROM APICall; //You load only one row to get the names

LET vDateRow = subfield(FieldName(3, 'FieldNames'), '_', 4); //To get the date

Drop Table FieldNames;

FinalTable:

LOAD

     event,

     count,

     $(vDateRow)     AS Date

FROM APICall;

Probably you must to do some changes to this script, but I think is a good approach to the solution.

Anonymous
Not applicable
Author

Exactly what I needed, thank you!

Small issues keep occurring... Now there seems to be an issue with the dates.

I have the date 2017-05-03 and this is exactly what  is read into vDateRow, but it doesn't stay that way when loaded into the table. I've tried several different ways but I can't seem to get it right.

LET vDateRow = subfield(FieldName(3, 'steps'),'_',4);                         (where vDateRow == 2017-05-03)

[InputTable]:     

LOAD

     Date($(vDateRow)) AS week_date,

     $(vDateRow) AS week_date_2,

     [event] as [event_community],

     [count] AS [count_community]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_steps]);

Screen Shot 2017-08-02 at 16.26.24.png

So the issue is that the real date is 2017-05-03 but it shows up as something completely different.

Thanks!

jmvilaplanap
Specialist
Specialist

Hi

Could you mark the response as correct?

The issue with the dates, seems like a date format issue, when you load the date, use the date# function to configure the format.

date#(DateField, 'FORMAT') as Date

Regards

Anonymous
Not applicable
Author

Hi,

It doesn't seem to work with date# either. I'll post a new question since it is a new topic.

A big thank you for all your help!

Regards