Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
event | count | __KEY_2017-06-02 |
---|---|---|
event1 | 250 | 1 |
event2 | 210 | 1 |
event3 | 200 | 1 |
event4 | 180 | 1 |
table_u0:
event_u0 | count_u0 | __KEY_2017-06-09 |
---|---|---|
event1 | 340 | 1 |
event2 | 300 | 1 |
event3 | 280 | 1 |
event4 | 250 | 1 |
output:
date | event1 | event2 | event3 | event4 |
---|---|---|---|---|
2017-06-02 | 250 | 210 | 200 | 180 |
2017-06-09 | 340 | 300 | 280 | 250 |
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-02 | 1 |
2017-06-09 | 1 |
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
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
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
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)
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!
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.
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]);
So the issue is that the real date is 2017-05-03 but it shows up as something completely different.
Thanks!
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
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