Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I've got an app with two tables:
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.
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
Thanks. How do I do that? If I edit the table in Sense and use Add field>Calculated field, there is no Count option.
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
You've lost me there I'm afraid. I don't know anything about scripts - I just drag and drop in files!
Thanks.
In the hub click on top left drop down / editor
in the script, select on the left auto generated script and add a line
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]);
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]);
That throws a syntax error:
you missed the , (comma) after Count.
Each field is ended with a comma at the end of the line.
Regards