topic concat in QlikView

concat

Bubskim — Thu, 08 Aug 2019 18:53:18 GMT

Please help me combine two emails into one so they show as one in my chart.

exampleemail

exampleemail@example.com

^ I would like these two above to show as exampleemail and be grouped together

Re: concat

Lisa_P — Fri, 09 Aug 2019 00:16:12 GMT

One way to achieve this is with a mapping table.

Either create inline or from an external table a simple lookup with oldname and new name.

RenameMap:

Load * inline [

OldName, NewName

exampleemail@example.com, exampleemail

];

Then when you are ready to load the field

Load

Applymap('RenameMap', Email) as Email,

Re: concat

Bubskim — Fri, 09 Aug 2019 12:30:52 GMT

Hi where am I putting each one of those? in the script?

I was able to do this

Load * inline [exampleemail@example.com, exampleemail];

but idk what to do with your bottom part?

Re: concat

lockematthewp — Fri, 09 Aug 2019 15:28:45 GMT

The easiest solution would be as follows:

Load ...

subfield([EmailAddress], '@', 1) as [EmailAddress],

From ...

This will basically remove the @whatever.com from every email address. Then any matches are automatically grouped together.

Re: concat

Bubskim — Fri, 09 Aug 2019 17:46:53 GMT

I do not get it.

In the load is this for the load script?

Here is what I did

Load

subfield([Sent By], 'exampleemail@example.com', 1) as [exampleemail],

Re: concat

Bubskim — Fri, 09 Aug 2019 18:17:05 GMT

also here is another example based on what you gave me to work with!

I need to have a specific name of a box truncate into 1. The text between is the name of the box with the type of alert like disk space.

load

subfield([Sent By], 'specificname*Disk_Space*', 1), If(WildMatch([Sent By], '*specificname*:Disk_Space*'), TextBetween ([Sent By], 'specificnamewithnumbers*','')) as [specificname Disk Space],

Re: concat

rutger_jansen — Mon, 12 Aug 2019 12:53:52 GMT

Hi,

Attached is what the other members are describing. Can you provide examples of your other question in a qvw for us to look at?

Re: concat

Bubskim — Mon, 12 Aug 2019 13:41:02 GMT

Hi thank you for doing that. I can use it for something else.

However, instead of displaying the sum I need to do the exampleemail@example.com as one = exampleemail . I do not need the sum, I need it to come out as one on my chart

Re: concat

rutger_jansen — Mon, 12 Aug 2019 13:48:49 GMT

Do you mean a count of unique email addresses, as per attached?

Re: concat

Bubskim — Mon, 12 Aug 2019 14:22:52 GMT

We may be getting close.

What is this in the load?

LOAD * INLINE [
email, size
exampleemail, 5
exampleemail@example.com, 15
];

Re: concat

rutger_jansen — Mon, 12 Aug 2019 14:31:25 GMT

That's sample data. You might be taking yours from a database or flat file. In that case you put the following lines ('Preceding load'):

LOAD
subfield([email], '@', 1) as [fixed_email],
*;

above the load statement, e.g. for an Excel file:

LOAD
subfield([email], '@', 1) as [fixed_email],
*;
email:
LOAD
email, size
FROM
[..\emails.xlsx]
(ooxml, embedded labels, table is Sheet1);

or from a database:

LOAD
subfield([email], '@', 1) as [fixed_email],
*;
SQL Select
email, size
from database.table;

or for

Re: concat

Brett_Bleess — Tue, 27 Aug 2019 15:12:01 GMT

Kimberly, did Rutger's final solution work for you? If so, please be sure to give credit by using the Accept as Solution button on the post that best helped with things. If you came up with a different solution, please consider posting that and mark it as the accepted solution so others will know for sure what worked in this case...

Regards,
Brett