LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jmorrissette

I'm guessing this is simple but I cant seem to find the answer.

This post (http://community.lavastorm.com/archi...php/t-163.html) seems to have given me a hint, but I cant seem to get this to work the way that I want. I am combining two data sets, lets call them User and Licenses. The User records contain a field called Activtations that is a multi line free text field which contains license keys. Sometimes the Activations field contains a single key and sometimes it contains multiple keys separated by spaces, commas, carriage returns and other unpredictable deliniators. To make it worse, sometimes the Activations field also contains other chaf such as notes as to how the keys were used. None the less I need to combine the two data sets using the Activation as the join. The bright spot is that the Activation field in License is well formatted and always contains one good key.

So here is a sample data set:

[License]
Activation:string, StartDate:string
"12345", 3/13/15
"12346", 3/14/15
"12347", 3/15/15
"12348", 3/16/15
"12349", 3/17/15

[User]
Name:string , Activations:string
"Bob","12345"
"Tom","12346, 12347"
"Allan","12348;12349 I sent this key yesterday"

The resulting output should be:
[Output]
Activation:string, StartDate:string, Name:string
"12345" , "3/13/15", "Bob"
"12346" , "3/14/15", "Tom"
"12347" , "3/15/15", "Tom"
"12348" , "3/16/15", "Allan"
"12349" , "3/17/15", "Allan"

I tried using an X-Ref Node with this output:

output 1 {
emit 1:*
where join.leftOrphan
}

output 2 {
emit *
where join.match
}

output 3 {
emit *
where strFind(2:Activations,1:Activation) >= 0
}

but it does not produce the correct results.
If it was working correctly pin 3 would contain the full output whereas pin 2 would contain just Bob's record. However Pins 2 and 3 work identically, only containing Bob.

Keep in mind in the production use case both sets of data would contain up to 100k rows and the graph would run several times per day.

Thoughts?
Attachments:
test.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlam

Hi J,
Am I correct in thinking that the license keys all follow the same character pattern i.e. Four lots of 5 alphanumeric characters separated by a dash?
If so, then I think you can use the regex functions to extract the license keys from the "Activations" string in the User file and then "expand from list" to give you a better table to join to the License data set.

Example attached:
Attachments:
test_vML.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

At this time, the only real way to do the match you need is with a Cartesian join.
The difficulty with the Cartesian is that it will quickly consume all available memory.

One way to get around this is to do a preliminary 3-digit match (Stage1) which will produce sub-groups of candidate records.
The second match (Stage2) then does a Cartesian within the 3-digit groups, effectively reducing the amount of memory required by 1000*1000 (or is that 100*100?) - still should help avoid the memory bounds.
Attachments:
test.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Jay, if I understand you correctly, you would like to take each activation and see if it's contained in any of the activation lists from the second source. The problem with using 'Activation' and 'Activations' as the keys is that you're looking to see if they equal. And 'Activation' is not compared with every other 'Activations'. To see what actually happens, look at the top X-Ref. Note the order at which the left and right records appear. When a record is NOT matched (from either left or right), its spot is marked and all records before that point (and including that record) will not be used for subsequent matches. I apologize if I butchered that explanation.

So make a long story short, if you want to compare each activation with every other from the second list, use 1's, i.e. TRUE, as your match keys. This will force each record from left to be compared with each record from the right. Since you want the matches, you can also accomplish this with a join inner. As the data volume grows from each side, you'll want to consider scalability. Depending on what you care about, there might be ways make this more efficient. For example, Michelle's example creates a master list of all activation numbers. Once you remove duplicates, then your original plan of using 'Activation' will work.
Attachments:
test_RY.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jmorrissette

Perfect, basically all I had to do was switch to a Join instead of xRef and get the output statement correct:

emit *
where strFind(2:Activations,1:Activation) > -1

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jmorrissette

scratch that, its not working. It works when I use a static data node but not when I use Excel. Why would that matter? I attached a zip file with both Excel files and the graph.
Attachments:
test.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

When you say not working, what do you mean? That you're only getting one match and not all six? I still think you need to use 1's as your match key to do the Cartesian join (thank you, Stony, for teaching me that term).

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jmorrissette

Ahh yes,
It is the 1's. What does that change? I don't quite understand the use of the 1's vs calling out the names of the fields.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

By putting in the 1's, then every record on the left is matched to every record on the right.

One warning; If you have a huge number of records, it will blow up for trying to use too much memory.
There are other methods to use when trying to solve this problem, but the simplest is to use the cartesian. (1's)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jmorrissette

Just so I understand the mechanics here, let get me this straight. Is it that since you are entering a matching integer on both sides, a 1 in this case, it is effectively creating a column in each record set with a value of 1 in every row, and therefore when you join on it you get the Cartesian result? And then from that full record set, since you are using the Where condition, you filter out the non-matching results?

I guess that would make sense.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

It doesn't physically create a column, but you've got the correct concept.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Originally posted by: jmorrissette
					

Just so I understand the mechanics here, let get me this straight. Is it that since you are entering a matching integer on both sides, a 1 in this case, it is effectively creating a column in each record set with a value of 1 in every row, and therefore when you join on it you get the Cartesian result? And then from that full record set, since you are using the Where condition, you filter out the non-matching results?


I guess that would make sense.
			

J,

As long as the key values are constant values and do not change it doesn't matter what the value is. 1 is an arbitrary integer value. It could be TRUE or FALSE or "A" or null or any value that is constant. As long as the two key values are the same.

The same concept can be used with an Agg node to return a single record. If you put a constant value in the GroupBy parameter then you will always get a single record. This is useful when you want to create a field with the record count or to sum a value across the entire dataset.

Hope that helps...

Larry