Highlighted

## Working with a numeric list

Enthusiast ,
May 04, 2020

Copy link to clipboard

Copied

I have a fixed numeric list, let's say that's a list of numbers from 1 to 50

myfixedlist=1,2,3,4,5 etc.etc

I then have another list which contains some of the values in the list above

variablelist = 10,12,20,50

I need to create another list that gives me all of the values in myfixedlist that is not in variablelist

Does anybody know of an efficient way to create this list in one swoop, using a CF command that doesn't involve a loop and lots of CFIF's

Enthusiast
| Enthusiast

Thanks @BKBK and @WolfShade. Both options work 🙂 I'm not sure which one is the most efficient, I'll have to flip a coin.

I did actually just come up with something most likely a little less efficient, I created a query in memory, added the data from myfixedlist and then did a query using NOT IN, it also worked, but I suspect your options are better. I put it in here anyway as option 3.

OPTION 1

<cfscript>
myfixedlist="7,8,9";
variablelist = "1,2,3,4,5,6,7,10";

listDiff = listFilter(myfixedlist,function(item){
return listFind(variablelist,item,",") == 0;
});
</cfscript>

<cfoutput>#listDiff#</cfoutput>

OPTION 2
<cfset myFixedList = "7,8,9" />

<cfset myVarList = "1,2,3,4,5,6,7,10" />

<cfset myNOTlist = "" />

<cfscript>

function getNotInFixed(item){

if(NOT ListFind(myVarList,item)){myNOTlist = ListAppend(myNOTlist,item);}
}

myMap = ListMap(myFixedList,getNotInFixed);

WriteOutput(myNOTlist);

</cfscript>

OPTION 3
(VariableList would be set elsewhere in the code)
<cfset MyFixedList = QueryNew("MyFixedListUID")>
<cfset QueryAddRow(MyFixedList,3)>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","7","1")>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","8","2")>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","9","3")>

<CFQUERY DBTYPE="query" NAME="GetNewList">
SELECT MyFixedListUID
FROM MyFixedList
WHERE MyFixedListUID NOT IN (<cfqueryparam value="#VariableList#" list="yes">)
</CFQUERY>

Views

2.4K

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

## Working with a numeric list

Enthusiast ,
May 04, 2020

Copy link to clipboard

Copied

I have a fixed numeric list, let's say that's a list of numbers from 1 to 50

myfixedlist=1,2,3,4,5 etc.etc

I then have another list which contains some of the values in the list above

variablelist = 10,12,20,50

I need to create another list that gives me all of the values in myfixedlist that is not in variablelist

Does anybody know of an efficient way to create this list in one swoop, using a CF command that doesn't involve a loop and lots of CFIF's

Enthusiast
| Enthusiast

Thanks @BKBK and @WolfShade. Both options work 🙂 I'm not sure which one is the most efficient, I'll have to flip a coin.

I did actually just come up with something most likely a little less efficient, I created a query in memory, added the data from myfixedlist and then did a query using NOT IN, it also worked, but I suspect your options are better. I put it in here anyway as option 3.

OPTION 1

<cfscript>
myfixedlist="7,8,9";
variablelist = "1,2,3,4,5,6,7,10";

listDiff = listFilter(myfixedlist,function(item){
return listFind(variablelist,item,",") == 0;
});
</cfscript>

<cfoutput>#listDiff#</cfoutput>

OPTION 2
<cfset myFixedList = "7,8,9" />

<cfset myVarList = "1,2,3,4,5,6,7,10" />

<cfset myNOTlist = "" />

<cfscript>

function getNotInFixed(item){

if(NOT ListFind(myVarList,item)){myNOTlist = ListAppend(myNOTlist,item);}
}

myMap = ListMap(myFixedList,getNotInFixed);

WriteOutput(myNOTlist);

</cfscript>

OPTION 3
(VariableList would be set elsewhere in the code)
<cfset MyFixedList = QueryNew("MyFixedListUID")>
<cfset QueryAddRow(MyFixedList,3)>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","7","1")>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","8","2")>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","9","3")>

<CFQUERY DBTYPE="query" NAME="GetNewList">
SELECT MyFixedListUID
FROM MyFixedList
WHERE MyFixedListUID NOT IN (<cfqueryparam value="#VariableList#" list="yes">)
</CFQUERY>

Views

2.4K

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 04, 2020 0
11 Replies 11
LEGEND ,
May 04, 2020

Copy link to clipboard

Copied

Hello, ACS,

You can use ListMap or ListReduce, this will iterate without you writing the loop code, and use a callback function to compare the value of the current index in said loop to the other list.  ONE cfif to check if that value exists in the other list.  If it does, don't add it to a new variable.

<cfset myFixedList = "1,2,3,4.." />

<cfset myVarList = "10,12,20,45.." />

<cfset myNOTlist = "" />

<cfscript>

getNotInFixed(item){

if(NOT ListFind(myVarList,item){myNOTlist = ListAppend(myNOTlist,item);}

}

myMap = ListMap(myFixedList,getNotInFixed);

WriteOutput(myNOTlist);

</cfscript>

HTH,

^ _ ^

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 04, 2020 0
Enthusiast ,
May 04, 2020

Copy link to clipboard

Copied

Thanks Wolfshade,

I updated the values to try and obtain a result, but it threw an error

<cfset myFixedList = "1,2,3,4" />

<cfset myVarList = "1,2,10,12,20,45" />

<cfset myNOTlist = "" />

<cfscript>

getNotInFixed(item){

if(NOT ListFind(myVarList,item){myNOTlist = ListAppend(myNOTlist,item);}

}

myMap = ListMap(myFixedList,getNotInFixed);

WriteOutput(myNOTlist);

</cfscript>

ERROR:

Invalid construct.
A script statement must end with ";".

The CFML compiler was processing:

A script statement beginning with getNotInFixed on line 9, column 5.
A cfscript tag beginning on line 7, column 2.

The error occurred in D:/temp/createfreetoplaylist.cfm: line 9

7 : <cfscript>
8 :
9 : getNotInFixed(item){
10 :
11 : if(NOT ListFind(myVarList,item){myNOTlist = ListAppend(myNOTlist,item);}

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 04, 2020 0
LEGEND ,
May 04, 2020

Copy link to clipboard

Copied

D'OH!  I flaked.. add the word "function" in front of getNotInFixed().

``function getNotInFixed(){``

FACEPALM

HTH,

^ _ ^

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 04, 2020 0
Enthusiast ,
May 05, 2020

Copy link to clipboard

Copied

Still throwing an error 😞 It looks like there are 6 open brackets and 5 closing brackets

<cfset myFixedList = "1,2,3,4" />

<cfset myVarList = "1,2,10,12,20,45" />

<cfset myNOTlist = "" />

<cfscript>

function getNotInFixed(item){

if(NOT ListFind(myVarList,item){myNOTlist = ListAppend(myNOTlist,item);}

}

myMap = ListMap(myFixedList,getNotInFixed);

WriteOutput(myNOTlist);

</cfscript>

Invalid CFML construct found on line 11 at column 40.
ColdFusion was looking at the following text:
{

The CFML compiler was processing:

A script statement beginning with if on line 11, column 9.
A script statement beginning with function on line 9, column 4.
A cfscript tag beginning on line 7, column 2.

The error occurred in D:/temp/create

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 05, 2020 0
LEGEND ,
May 05, 2020

Copy link to clipboard

Copied

I'm missing a parenthesis.

if(NOT ListFind(myVarList,item)){myNOTlist = ListAppend(myNOTlist,item);}

V/r,

^ _ ^

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 05, 2020 0
Adobe Community Professional ,
May 05, 2020

Copy link to clipboard

Copied

Use the function listFilter.

Example:

``````<cfscript>
myfixedlist="1,2,3,4,5,6,7,8,9,10";
variablelist = "2,4,6,10";

listDiff = listFilter(myfixedlist,function(item){
return listFind(variablelist,item,",") == 0;
});
</cfscript>

<cfoutput>#listDiff#</cfoutput>``````

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 05, 2020 0
Enthusiast ,
May 05, 2020

Copy link to clipboard

Copied

Thanks @BKBK and @WolfShade. Both options work 🙂 I'm not sure which one is the most efficient, I'll have to flip a coin.

I did actually just come up with something most likely a little less efficient, I created a query in memory, added the data from myfixedlist and then did a query using NOT IN, it also worked, but I suspect your options are better. I put it in here anyway as option 3.

OPTION 1

<cfscript>
myfixedlist="7,8,9";
variablelist = "1,2,3,4,5,6,7,10";

listDiff = listFilter(myfixedlist,function(item){
return listFind(variablelist,item,",") == 0;
});
</cfscript>

<cfoutput>#listDiff#</cfoutput>

OPTION 2
<cfset myFixedList = "7,8,9" />

<cfset myVarList = "1,2,3,4,5,6,7,10" />

<cfset myNOTlist = "" />

<cfscript>

function getNotInFixed(item){

if(NOT ListFind(myVarList,item)){myNOTlist = ListAppend(myNOTlist,item);}
}

myMap = ListMap(myFixedList,getNotInFixed);

WriteOutput(myNOTlist);

</cfscript>

OPTION 3
(VariableList would be set elsewhere in the code)
<cfset MyFixedList = QueryNew("MyFixedListUID")>
<cfset QueryAddRow(MyFixedList,3)>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","7","1")>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","8","2")>
<cfset Temp = QuerySetCell(MyFixedList, "MyFixedListUID","9","3")>

<CFQUERY DBTYPE="query" NAME="GetNewList">
SELECT MyFixedListUID
FROM MyFixedList
WHERE MyFixedListUID NOT IN (<cfqueryparam value="#VariableList#" list="yes">)
</CFQUERY>

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 05, 2020 0
Adobe Community Professional ,
May 05, 2020

Copy link to clipboard

Copied

I don't understand. You asked initially if anybody knows "of an efficient way to create this list in one swoop, using a CF command that doesn't involve a loop and lots of CFIF's".

Then you yourself come up with a solution involving a query and 5 function invocations, with one function per list element, hence an O(n) solution. That is, a solution that will grow proportionately with the length of the list. Then you proceed to mark it as part of the answer!

The mind boggles.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 05, 2020 0
LEGEND ,
May 05, 2020

Copy link to clipboard

Copied

Well, I have to agree that your third option is the least efficient.  But you should do what feels most comfortable, I guess.  Keeping in mind, of course, that if there are other people working on this it should be logical to everyone.  Or if there will come a day where you leave for greener pastures and someone else will have to pick up your mantle.  <!--- Lots of comments are helpful in situations like that. --->

I hadn't thought of the route BKBK suggested.  Seems just as efficient as my suggestion.  But unless you're working with a list of IDs that ranges in the upper-six figure total (or more), I wouldn't worry about efficiency.

V/r,

^ _ ^

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 05, 2020 0
Enthusiast ,
May 19, 2020

Copy link to clipboard

Copied

I marked the thread as the answer because it had all three options in there, the top two of course being the better options. Mine was just another way of doing it. My list is not huge so it would have also done the trick (for me), I didn't use my option I went with one of the others.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 19, 2020 0
LATEST
Adobe Community Professional ,
May 19, 2020

Copy link to clipboard

Copied

Fair enough.

Likes

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 19, 2020 0