0

Help with a search

I'm trying to find records where a group of numbers match another set of numbers. Here is a demo of what I'm trying to do.

let x := array(["545,577,831"], ["121,545,577"]);
let y := [{
            Loc: 115,
            RR: [545, 577, 831]
        }, {
            Loc: 116,
            RR: [121, 545, 577]
        }, {
            Loc: 117,
            RR: [121, 545, 831]
        }, {
            Loc: 118,
            RR: [121, 545, 615]
        }, {
            Loc: 119,
            RR: [121, 545, 615]
        }, {
            Loc: 136,
            RR: [121, 545, 565]
        }, {
            Loc: 137,
            RR: [121, 545, 565]
        }, {
            Loc: 139,
            RR: [541, 545, 565]
        }, {
            Loc: 144,
            RR: [121, 545, 565]
        }, {
            Loc: 145,
            RR: [121, 541, 545]
        }];
let z := for loop3 in x do
        let subArray := split(loop3, ",");
        {
            Group: loop3,
            Locs: y[var sub3 := split(text(RR), ",");
                count(sub3[var sub3a := this;
                            count(subArray[= sub3a]) > 0]) > 0].Loc
        }
    end;
z

I'm trying to find records in y that match the group of numbers in x. My current code always finds all because there is one number in each group in x that is in all of the records in y. I'm only trying to find records that match all three, but the array in an array is stumping me.

1 reply

null
    • Fred
    • 11 mths ago
    • Reported - view

    Well I think I solved it:

    let z := for loop3 in x do
        let subArray := split(loop3, ",");
        {
            Group: loop3,
            Locs: y[var sub3 := text(RR);
                extractx(sub3, "\[(.+)\]", "$1") = text(loop3)].Loc
        }
    end;

    It just took me a while to figure out how the data is represented at different steps.

    I started with this line:

    let a := item(y, 1).text(RR);
let b := item(x, 1);
if a = b then 1 else 100 end

    I got 100. I wondered why? Then when I looked at each variable separately I got:

    a = [121,545,577]
     

    b = 121,545,577

    Very different data sets. So I wondered if just removing the [ ] would make them equal? So I did this test:

    if extractx(a, "\[(.+)\]", "$1") = b then
    1
else
    100
end

    And I got 1.

    Then I plugged in the extractx into the formula for z and it worked. I can change the numbers in x and I get the proper results.

Login to reply

Content aside

  • Status Answered
  • 11 mths agoLast active
  • 1Replies
  • 39Views
  • 1 Following