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: 
awigham
Contributor
Contributor

Table field association

Hi all,

I've got an app with two tables:

  • A csv file showing all user interactions with a website
  • An xlsx file showing all user profiles associated with that website

The csv file has a field  "User Affected Email" which lists the email address of the user account affected by the interation. The xlsxfile has a field " Email" which lists the email address associated with a user account.

If a associate the tables, I get an association "Email-User Affected Email" which then comes through into the app as a dimension "Email-User Affected Email".

The issue I have is trying to be able to show the number of appearances of an email address on the interaction sheet (ie number of time interacted with in last month) compared to the number of user profiles held for a particular client (a  field on the xlsx file). If I count by "Email-User Affected Email" I only ever get the count of addresses from the xlsx, whereas I need the count by the csv.

Labels (2)
10 Replies
martinpohl
Partner - Master
Partner - Master

Hi,

never count on a linked field because you can't reduce the count on one explicite table.

Create a Count field in table 1 (eg 1 as CountActions) and sum this field. So you have the number of actions for each email.

regards

awigham
Contributor
Contributor
Author

Thanks. How do I do that? If I edit the table in Sense and use Add field>Calculated field, there is no Count option.

martinpohl
Partner - Master
Partner - Master

You have to add the field in the script.

Go to your script (maybe there is a autogeneratet tab, then edit and agree) and add manual a line

Regards

awigham
Contributor
Contributor
Author

You've lost me there I'm afraid. I don't know anything about scripts - I just drag and drop in files!

Thanks.

martinpohl
Partner - Master
Partner - Master

In the hub click on top left drop down / editor

clipboard_image_0.png

in the script, select on the left auto generated script and add a line

clipboard_image_1.png

awigham
Contributor
Contributor
Author

Is the "1" supposed to be something relevant to my data? Adding in "1 as Count" doesn't work.

This is what I have:

[Copy of audit-logs]:
LOAD
[Date / Time],
[User Requesting Email],
[User Affected Email] AS [Email-User Affected Email],
APPLYMAP( 'DeviceMapping', [Device]) AS [Device],
APPLYMAP( 'Mobile App TypeMapping', [Mobile App Type]) AS [Mobile App Type],
APPLYMAP( 'Device InfoMapping', [Device Info]) AS [Device Info],
[Event Type / Description]
FROM [lib://OneDrive - REDACTED/Copy of audit-logs.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[TravellerProfiles_Aug-19]:
LOAD
[Name],
[Email] AS [Email-User Affected Email],
[Client]
FROM [lib://OneDrive - REDACTED/TravellerProfiles_Aug-19.xlsx]
(ooxml, embedded labels, table is [TravellerProfiles_Aug-19]);

martinpohl
Partner - Master
Partner - Master

add bold line

 

 

[Copy of audit-logs]:
LOAD

1 as Count,
[Date / Time],
[User Requesting Email],
[User Affected Email] AS [Email-User Affected Email],
APPLYMAP( 'DeviceMapping', [Device]) AS [Device],
APPLYMAP( 'Mobile App TypeMapping', [Mobile App Type]) AS [Mobile App Type],
APPLYMAP( 'Device InfoMapping', [Device Info]) AS [Device Info],
[Event Type / Description]
FROM [lib://OneDrive - REDACTED/Copy of audit-logs.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[TravellerProfiles_Aug-19]:
LOAD
[Name],
[Email] AS [Email-User Affected Email],
[Client]
FROM [lib://OneDrive - REDACTED/TravellerProfiles_Aug-19.xlsx]
(ooxml, embedded labels, table is [TravellerProfiles_Aug-19]);

awigham
Contributor
Contributor
Author

That throws a syntax error:

clipboard_image_0.png

martinpohl
Partner - Master
Partner - Master

you missed the , (comma) after Count.

Each field is ended with a comma at the end of the line.

Regards