Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

adding two columns

Hi all,

I have two tables signupactivity and useractivity.

I have used signupactivity table in other sheets. Now i need to add a new table useractivity for new sheet.

One of the field from signupactivity i.e referrer_email_type_id which has the information about email_type_id.

In the same way "status" field from useractivity table has the information about email_type_id.

For example: if referrer_email_type_id  has info about email_type_id(44,45,46,47) and

status has info about email_type_id(2,3,4,8).

Now i need to add the both the columns into single column.

I used join, concatenate it din't worked.

The issue is signupactivity  is using invitation_id has association key to link with other table.

This invitation_id  is not in useractivity table. I have user_id in useractivity table.

when i used "join" between these to table i am getting sync error or circular reference error.


(I need to create a "Pivot table" which displays the count of email_type_id in a column. which can be filtered on client basis.)

Please anyone help on this where i need to add both the above columns and table.

I think this is clear, if not please ask.

Thanks,

Pramod

10 Replies
ogster1974
Honored Contributor II

Re: adding two columns

I think you need to revisit your data model.

Could you not have a single fact table with common activity information named the same e.g. email_type_id, action date etc.. and a type field where you id your activity type as either Sign Up or User with keys to link off to the user dimension and sign up dimension table where you store details unique to that dimension then these types of issues wont arise.

Regards

Andy

Not applicable

Re: adding two columns

hi Andy Weir,

Din't get you. would you explain it briefly.

Thanks,

Pramod

ogster1974
Honored Contributor II

Re: adding two columns

Why not have one table to store all your activities in with common attributes like %SiteId, %UserId, Date, Type of Action i.e. Signup, user visit etc....  With keys off to user details, site details etc... in a classic star schema that way you will avoid getting stuck on joins, circular references as your fact data is all in the one table.

At the moment I think you storing values separately when they could be in the same field which you can get away with in SQL Relational DBs but Qlik Data model expects it in a certain way.

referrer_email_type_id, email_type_id its just an email type so why not just store them in one table as email type and add a field to give it context email=referrer  

Use of set analysis in your measures will extract the relevant data for you in your charts.

Hope this makes sense.

Andy

Not applicable

Re: adding two columns

Hi andy,

Thanks for the response.

Yes but now we are not able to change the data model.

will you please explain how can i use set analysis to display count(email_type_id) in pivot table.

The email_type_id should come in one single column and its respective count in another column.

Thanks,

Pramod

neetha_p
Honored Contributor

Re: adding two columns

hi pramod,

rename columns to same name

ogster1974
Honored Contributor II

Re: adding two columns

I think it will help us to answer your question if you put up a mock up sample of what you want the piviot to look like and some sample data.

Not applicable

Re: adding two columns

Hi andy,

Please find the sample data and how it should look.

I have tried already by joining the data. the counts are showing properly.

The issue is it is not listing under the client id's when i used the filter pane you can see in the image attached.

Thanks,

Pramod

Not applicable

Re: adding two columns

hi neetha,

I renamed the column  to the same name the issue is when i load the useractivity table with fields user_id and "status" as email_type_id,  i am getting the sync error or circular reference error.


This is because email_type_id and user_id both is using for association. This is when i loaded without join.

When i loaded with joining the two tables, i am only loading the "status" as email_type_id from useractivity table, at that time email_type_id is loading but all other fields are empty. this is affecting me when i was filtering through filter pane.

I think you have understand the issue.

Thanks,

Pramod

neetha_p
Honored Contributor

Re: adding two columns

please can you paste the code here

Community Browser