Highlighted

Sort / CFLOOP

Participant ,
Nov 05, 2018

Copy link to clipboard

Copied

I have an excel that I read and create a query object using this code

<cfspreadsheet action="read" src="#theFile#" query="qData" headerrow="1" excludeHeaderRow ="true"  columns="9,15,51,56,57,58,59"   />

So now I have a query object that I output and it outputs in the order that is in the excel.

I loop through it like this

<cfloop query="qData">

and I get this

IDValue
1Adams
2Jones
3Smith
4Johnson
5Williams

Now I need to do the order in reverse like this

IDValues
5Williams
4Johnson
3Smith
2Jones
1Adams

  Can I sort by a loop? How ?

Yup.  Basically, you are going through the query backwards, starting at the last record, moving "step -1" (backwards) to the first record.  The "idx" is the index based upon where in the iterative loop you are, and the bracket notation is getting the data from that index of the query object.

HTH,

^ _ ^

Views

943

Likes

Translate

Translate

Report

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

Sort / CFLOOP

Participant ,
Nov 05, 2018

Copy link to clipboard

Copied

I have an excel that I read and create a query object using this code

<cfspreadsheet action="read" src="#theFile#" query="qData" headerrow="1" excludeHeaderRow ="true"  columns="9,15,51,56,57,58,59"   />

So now I have a query object that I output and it outputs in the order that is in the excel.

I loop through it like this

<cfloop query="qData">

and I get this

IDValue
1Adams
2Jones
3Smith
4Johnson
5Williams

Now I need to do the order in reverse like this

IDValues
5Williams
4Johnson
3Smith
2Jones
1Adams

  Can I sort by a loop? How ?

Yup.  Basically, you are going through the query backwards, starting at the last record, moving "step -1" (backwards) to the first record.  The "idx" is the index based upon where in the iterative loop you are, and the bracket notation is getting the data from that index of the query object.

HTH,

^ _ ^

Views

944

Likes

Translate

Translate

Report

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

Copy link to clipboard

Copied

There are a couple of ways to sort the query object, I believe.

One way would be to loop the object in reverse, if you are strictly going for a reverse-only output.

<cfoutput>

<cfloop from="#qData.recordcount#" to="1" step="-1" index="idx">

    #qData.ID[idx]#        #qData.Values[idx]#<br />

</cfloop>

</cfoutput>

If the IDs are always sequential, you can reverse sort like so:

<cfset qDdata.sort(qDdata.findColumn("ID"), FALSE)>

Then output as you normally would.  The "FALSE" means DESCENDING; "TRUE" would be ASCENDING.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 05, 2018 0
Participant ,
Nov 05, 2018

Copy link to clipboard

Copied

Sorry, Wolf. I had some bad info and didn't phrase the question correctly

This is the query object . When it loops the first time it outputs like this

    

TITLECITYFIRST_NAMELAST_NAME
Mr.DetroitSteveSmith
Ms.VA BeachRonMoore
Mr.MadisonPeterSimpkins
Mr.ClevelandJohn

Warner

There is NO ID column

Is there a way to sort these via cfloop so that it goes Warner, Simpkins, Moore Smith?

Likes

Translate

Translate

Report

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

Copy link to clipboard

Copied

No, because that's not alphabetical or reverse-alphabetical.  Is there ANY kind of sequential ID being provided?

If you wanted it sorted alphabetically or reverse-alphabetically, then the Java sort that I suggested could be used on LAST_NAME, or FIRST_NAME, or CITY.

If there are no IDs, then the first option I suggested should work, if you are truly sorting reverse of the information as it was constructed.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 05, 2018 0
Participant ,
Nov 05, 2018

Copy link to clipboard

Copied

This option should work?


  1. <cfoutput> 
  2. <cfloop from="#qData.recordcount#" to="1" step="-1" index="idx"
  3.     #qData.ID[idx]#        #qData.Values[idx]#<br /> 
  4. </cfloop> 
  5. </cfoutput> 

Likes

Translate

Translate

Report

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

Copy link to clipboard

Copied

Yup.  Basically, you are going through the query backwards, starting at the last record, moving "step -1" (backwards) to the first record.  The "idx" is the index based upon where in the iterative loop you are, and the bracket notation is getting the data from that index of the query object.

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 05, 2018 0
Participant ,
Nov 05, 2018

Copy link to clipboard

Copied

Yes, this is the correct answer. As always thank you!

Likes

Translate

Translate

Report

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

Copy link to clipboard

Copied

You're welcome, and thank you for marking my answer as correct.  I do appreciate it.

V/r,

^ _ ^

Likes

Translate

Translate

Report

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