How to build an array from list and cfquery?

Question

I'm trying to build an array that will give me all values from my list and then based on the records from the cfquery I wan to get values for years. To make this more clear I will show you below how my array should look like:

time slot   year2015   year 2016
0700            0          0
0715            1          0
0730            0          0
0745            0          0
0800            0          5

and so on. My time slot values are stored in <cfset dataList> and that looks like this:

<cfloop list="#ListGetAt(dataList,i,",")#" index="k" delimiters="|">
        <cfoutput>#k#</cfoutput>
    </cfloop>

0600 0615 0630 0645 0700 0715 0730 0745 0800 0815 0830 0845 0900 0915 0930 0945 1000 1015 1030 1045 1100 1115 1130 1145 1200 1215 1230 1245 1300 1315 1330 1345 1400 1415 1430 1445 1500 1515 1530 1545 1600 1615 1630 1645 1700 1800 1900 2000 2100 2200 2300 0000

My time slot, years and values for each year are outputted from cfquery that looks like this:

<cfoutput query="getRecords" group="YearRecord">
    <cfoutput>
        #TimeSlot# - #YearRecord# - #ValueYear#<br/>
    </cfoutput> 
</cfoutput>

0830 - 2015 - 2
0915 - 2015 - 2
1000 - 2015 - 1
0630 - 2016 - 1
0800 - 2016 - 1

My records are coming from two different locations. Time slots from my list and records with valid values from my cfquery. So I want to build my array that contains all records from my list and for each record I want to output year column with the valid value. So I tried first to populate my array with the elements from the list:

<cfset myArray=ArrayNew(1)>

<cfloop list="#ListGetAt(dataList,i,",")#" index="z" delimiters="|">
    <cfoutput>
        #myArray[z]#
    </cfoutput>
</cfloop>

and this code give me an error:

Type: Expression ********** Line: 253 ********** ********** The element at position 600 of dimension 1, of array variable &quot;MYARRAY,&quot; cannot be found.

Also after I populate array I would have to add two more elements to that array. Year and values for each year. If anyone knows what I'm doing wrong and what is the best way to do this please let me know. Thanks in advance.

Why not just use a single [PIVOT](https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx) query or even the older `SUM(CASE...)` technique? — Leigh, Mar 23 '16 at 15:57
I tried that but later I had an issue to loop inside the list. — espresso_coffee, Mar 23 '16 at 16:23
It's really complex, that's why I tried to output everything to array and then loop over. Thanks for your help. — espresso_coffee, Mar 23 '16 at 17:47
IMO sounds like the multiple queries and arrays are more complex. A pivot query should be simpler. Run a single query and loop. — Leigh, Mar 23 '16 at 18:48

Bonanza · Answer 1 · 2016-03-23T15:53:24.507

At begining. Don't know Why everywhere You are using ListGetAt(dataList,i,","). Is that inside bigger loop?

This code

<cfset myArray=ArrayNew(1)>

<cfloop list="#ListGetAt(dataList,i,",")#" index="z" delimiters="|">
    <cfoutput>
        #myArray[z]#
    </cfoutput>
</cfloop>

is throwing error because myArray is empty when you are looping over your list. If you want to fill it that way you shuld do it like that

<cfset myArray=ArrayNew(1)>
<cfset yourList = ListGetAt(dataList,i,",")>
<cfloop list="#yourList#" index="z" delimiters="|">
    <cfset myArray.append(z)>
</cfloop>

or simply

<cfset yourList = ListGetAt(dataList,i,",")>
<cfset myArray = ListToArray(yourList)>

But if i understand it well you need 2 dimensional array. You should do it like that.

<cfset myArray=ArrayNew(2)>
<cfset yourList = ListGetAt(dataList,i,",")>
<cfloop list="#yourList#" index="z" delimiters="|">
    <!--- we will append array with our value. later you can append to this year and other values from query --->
    <cfset myArray.append([z])>
</cfloop>

EDIT 1 Maybe you should think about Struct not about array? Create struct with all your time slots as key. I will use short notation.

<cfset str = {}>
<cfloop list="#yourList#" index="z" delimiters="|">
    <cfset str[z] = {'y2015'=0,'y2016'=0,'valYear'=0}>
</cfloop>

<cfoutput query="getR" group="theYear">
    <cfif theYear eq 2015>
        <cfset str[z]['y2015'] = theCount>
    <cfelse>
        <cfset str[z]['y2016'] = theCount>
    </cfif>
</cfoutput>
<cfdump var="#str#">

Now you can use StructKeyList to get List of key from that stuct, to loop over that struct.

I tried this: so my records were on the right place but they are duplicated 5 times. if I remove my extra cfoutput then I'm getting just first record for 2015 and first records for 2016. How this can be fixed? — espresso_coffee, Mar 23 '16 at 15:33
Never use inside . It never finishes well. I'm updating my answer with another solution. Description in query is that timeslot from array ? — Bonanza, Mar 23 '16 at 15:46
Yes time slot is description. If I have two dimensional array like: 1. 0600 2. 0 3. 0615 4. 0 5. 0630 6. 2 How I can loop through that array to get my values from positions? — espresso_coffee, Mar 23 '16 at 16:08
Two dimensional array is like array inside array. So first to iterate on first dimension and inside that loop to create second loop which is looping over element returned from outer loop. — Bonanza, Mar 23 '16 at 16:12

PatNowak · Answer 2 · 2016-03-24T10:28:52.323

1

When you want to add something to array use arrayAppend. Remember that's a function, so please it:

arrayAppend(myArray, thingsToAdd);

Only on Lucee or in CF2016 it works with manner:

myArray.arrayAppend(thingToAdd);

edited Mar 24 '16 at 10:28

answered Mar 23 '16 at 15:45

PatNowak

5,721
1
25
31

ColdFusion 2016 supports `myArray.append(thingToAdd);` – John Whish Mar 23 '16 at 18:08

How to build an array from list and cfquery?

2 Answers2