6 Replies Latest reply: Jun 17, 2015 4:20 AM by chris goudy RSS

    Loop through fields to change fieldname/data type

    chris goudy

      I am pulling in a CSV file which contains the field names below. Each field contains the values for one of 5 measures (UN = unit, KG = kilogram, CU = Counting Unit, R = Rand, R/HP = Rand Historical Price), for the month and year (MTH/3/10) specified.


      I want to loop through each field, changing the name from [UN MTH/3/10 (Absolute)] to 201003, and I want to extract the 5 measures and turn each into its own field.


      Capture.PNG

       

      CSVFieldNames:

        [UN MTH/3/10  (Absolute)],

          [UN MTH/4/10  (Absolute)],

          [UN MTH/5/10  (Absolute)],

          [UN MTH/6/10  (Absolute)],

          [UN MTH/7/10  (Absolute)],

          [UN MTH/8/10  (Absolute)],

          [UN MTH/9/10  (Absolute)],

          [UN MTH/10/10  (Absolute)],

          [UN MTH/11/10  (Absolute)],

          [UN MTH/12/10  (Absolute)],

          [UN MTH/1/11  (Absolute)],

          [UN MTH/2/11  (Absolute)],

          [UN MTH/3/11  (Absolute)],

          [UN MTH/4/11  (Absolute)],

          [UN MTH/5/11  (Absolute)],

          [UN MTH/6/11  (Absolute)],

          [UN MTH/7/11  (Absolute)],

          [UN MTH/8/11  (Absolute)],

          [UN MTH/9/11  (Absolute)],

          [UN MTH/10/11  (Absolute)],

          [UN MTH/11/11  (Absolute)],

          [UN MTH/12/11  (Absolute)],

          [UN MTH/1/12  (Absolute)],

          [UN MTH/2/12  (Absolute)],

          [UN MTH/3/12  (Absolute)],

          [UN MTH/4/12  (Absolute)],

          [UN MTH/5/12  (Absolute)],

          [UN MTH/6/12  (Absolute)],

          [UN MTH/7/12  (Absolute)],

          [UN MTH/8/12  (Absolute)],

          [UN MTH/9/12  (Absolute)],

          [UN MTH/10/12  (Absolute)],

          [UN MTH/11/12  (Absolute)],

          [UN MTH/12/12  (Absolute)],

          [UN MTH/1/13  (Absolute)],

          [UN MTH/2/13  (Absolute)],

          [UN MTH/3/13  (Absolute)],

          [UN MTH/4/13  (Absolute)],

          [UN MTH/5/13  (Absolute)],

          [UN MTH/6/13  (Absolute)],

          [UN MTH/7/13  (Absolute)],

          [UN MTH/8/13  (Absolute)],

          [UN MTH/9/13  (Absolute)],

          [UN MTH/10/13  (Absolute)],

          [UN MTH/11/13  (Absolute)],

          [UN MTH/12/13  (Absolute)],

          [UN MTH/1/14  (Absolute)],

          [UN MTH/2/14  (Absolute)],

          [UN MTH/3/14  (Absolute)],

          [UN MTH/4/14  (Absolute)],

          [UN MTH/5/14  (Absolute)],

          [UN MTH/6/14  (Absolute)],

          [UN MTH/7/14  (Absolute)],

          [UN MTH/8/14  (Absolute)],

          [UN MTH/9/14  (Absolute)],

          [UN MTH/10/14  (Absolute)],

          [UN MTH/11/14  (Absolute)],

          [UN MTH/12/14  (Absolute)],

          [UN MTH/1/15  (Absolute)],

          [UN MTH/2/15  (Absolute)],

          [KG MTH/3/10  (Absolute)],

          [KG MTH/4/10  (Absolute)],

          [KG MTH/5/10  (Absolute)],

          [KG MTH/6/10  (Absolute)],

          [KG MTH/7/10  (Absolute)],

          [KG MTH/8/10  (Absolute)],

          [KG MTH/9/10  (Absolute)],

          [KG MTH/10/10  (Absolute)],

          [KG MTH/11/10  (Absolute)],

          [KG MTH/12/10  (Absolute)],

          [KG MTH/1/11  (Absolute)],

          [KG MTH/2/11  (Absolute)],

          [KG MTH/3/11  (Absolute)],

          [KG MTH/4/11  (Absolute)],

          [KG MTH/5/11  (Absolute)],

          [KG MTH/6/11  (Absolute)],

          [KG MTH/7/11  (Absolute)],

          [KG MTH/8/11  (Absolute)],

          [KG MTH/9/11  (Absolute)],

          [KG MTH/10/11  (Absolute)],

          [KG MTH/11/11  (Absolute)],

          [KG MTH/12/11  (Absolute)],

          [KG MTH/1/12  (Absolute)],

          [KG MTH/2/12  (Absolute)],

          [KG MTH/3/12  (Absolute)],

          [KG MTH/4/12  (Absolute)],

          [KG MTH/5/12  (Absolute)],

          [KG MTH/6/12  (Absolute)],

          [KG MTH/7/12  (Absolute)],

          [KG MTH/8/12  (Absolute)],

          [KG MTH/9/12  (Absolute)],

          [KG MTH/10/12  (Absolute)],

          [KG MTH/11/12  (Absolute)],

          [KG MTH/12/12  (Absolute)],

          [KG MTH/1/13  (Absolute)],

          [KG MTH/2/13  (Absolute)],

          [KG MTH/3/13  (Absolute)],

          [KG MTH/4/13  (Absolute)],

          [KG MTH/5/13  (Absolute)],

          [KG MTH/6/13  (Absolute)],

          [KG MTH/7/13  (Absolute)],

          [KG MTH/8/13  (Absolute)],

          [KG MTH/9/13  (Absolute)],

          [KG MTH/10/13  (Absolute)],

          [KG MTH/11/13  (Absolute)],

          [KG MTH/12/13  (Absolute)],

          [KG MTH/1/14  (Absolute)],

          [KG MTH/2/14  (Absolute)],

          [KG MTH/3/14  (Absolute)],

          [KG MTH/4/14  (Absolute)],

          [KG MTH/5/14  (Absolute)],

          [KG MTH/6/14  (Absolute)],

          [KG MTH/7/14  (Absolute)],

          [KG MTH/8/14  (Absolute)],

          [KG MTH/9/14  (Absolute)],

          [KG MTH/10/14  (Absolute)],

          [KG MTH/11/14  (Absolute)],

          [KG MTH/12/14  (Absolute)],

          [KG MTH/1/15  (Absolute)],

          [KG MTH/2/15  (Absolute)],

          [CU MTH/3/10  (Absolute)],

          [CU MTH/4/10  (Absolute)],

          [CU MTH/5/10  (Absolute)],

          [CU MTH/6/10  (Absolute)],

          [CU MTH/7/10  (Absolute)],

          [CU MTH/8/10  (Absolute)],

          [CU MTH/9/10  (Absolute)],

          [CU MTH/10/10  (Absolute)],

          [CU MTH/11/10  (Absolute)],

          [CU MTH/12/10  (Absolute)],

          [CU MTH/1/11  (Absolute)],

          [CU MTH/2/11  (Absolute)],

          [CU MTH/3/11  (Absolute)],

          [CU MTH/4/11  (Absolute)],

          [CU MTH/5/11  (Absolute)],

          [CU MTH/6/11  (Absolute)],

          [CU MTH/7/11  (Absolute)],

          [CU MTH/8/11  (Absolute)],

          [CU MTH/9/11  (Absolute)],

          [CU MTH/10/11  (Absolute)],

          [CU MTH/11/11  (Absolute)],

          [CU MTH/12/11  (Absolute)],

          [CU MTH/1/12  (Absolute)],

          [CU MTH/2/12  (Absolute)],

          [CU MTH/3/12  (Absolute)],

          [CU MTH/4/12  (Absolute)],

          [CU MTH/5/12  (Absolute)],

          [CU MTH/6/12  (Absolute)],

          [CU MTH/7/12  (Absolute)],

          [CU MTH/8/12  (Absolute)],

          [CU MTH/9/12  (Absolute)],

          [CU MTH/10/12  (Absolute)],

          [CU MTH/11/12  (Absolute)],

          [CU MTH/12/12  (Absolute)],

          [CU MTH/1/13  (Absolute)],

          [CU MTH/2/13  (Absolute)],

          [CU MTH/3/13  (Absolute)],

          [CU MTH/4/13  (Absolute)],

          [CU MTH/5/13  (Absolute)],

          [CU MTH/6/13  (Absolute)],

          [CU MTH/7/13  (Absolute)],

          [CU MTH/8/13  (Absolute)],

          [CU MTH/9/13  (Absolute)],

          [CU MTH/10/13  (Absolute)],

          [CU MTH/11/13  (Absolute)],

          [CU MTH/12/13  (Absolute)],

          [CU MTH/1/14  (Absolute)],

          [CU MTH/2/14  (Absolute)],

          [CU MTH/3/14  (Absolute)],

          [CU MTH/4/14  (Absolute)],

          [CU MTH/5/14  (Absolute)],

          [CU MTH/6/14  (Absolute)],

          [CU MTH/7/14  (Absolute)],

          [CU MTH/8/14  (Absolute)],

          [CU MTH/9/14  (Absolute)],

          [CU MTH/10/14  (Absolute)],

          [CU MTH/11/14  (Absolute)],

          [CU MTH/12/14  (Absolute)],

          [CU MTH/1/15  (Absolute)],

          [CU MTH/2/15  (Absolute)],

          [R MTH/3/10  (Absolute)],

          [R MTH/4/10  (Absolute)],

          [R MTH/5/10  (Absolute)],

          [R MTH/6/10  (Absolute)],

          [R MTH/7/10  (Absolute)],

          [R MTH/8/10  (Absolute)],

          [R MTH/9/10  (Absolute)],

          [R MTH/10/10  (Absolute)],

          [R MTH/11/10  (Absolute)],

          [R MTH/12/10  (Absolute)],

          [R MTH/1/11  (Absolute)],

          [R MTH/2/11  (Absolute)],

          [R MTH/3/11  (Absolute)],

          [R MTH/4/11  (Absolute)],

          [R MTH/5/11  (Absolute)],

          [R MTH/6/11  (Absolute)],

          [R MTH/7/11  (Absolute)],

          [R MTH/8/11  (Absolute)],

          [R MTH/9/11  (Absolute)],

          [R MTH/10/11  (Absolute)],

          [R MTH/11/11  (Absolute)],

          [R MTH/12/11  (Absolute)],

          [R MTH/1/12  (Absolute)],

          [R MTH/2/12  (Absolute)],

          [R MTH/3/12  (Absolute)],

          [R MTH/4/12  (Absolute)],

          [R MTH/5/12  (Absolute)],

          [R MTH/6/12  (Absolute)],

          [R MTH/7/12  (Absolute)],

          [R MTH/8/12  (Absolute)],

          [R MTH/9/12  (Absolute)],

          [R MTH/10/12  (Absolute)],

          [R MTH/11/12  (Absolute)],

          [R MTH/12/12  (Absolute)],

          [R MTH/1/13  (Absolute)],

          [R MTH/2/13  (Absolute)],

          [R MTH/3/13  (Absolute)],

          [R MTH/4/13  (Absolute)],

          [R MTH/5/13  (Absolute)],

          [R MTH/6/13  (Absolute)],

          [R MTH/7/13  (Absolute)],

          [R MTH/8/13  (Absolute)],

          [R MTH/9/13  (Absolute)],

          [R MTH/10/13  (Absolute)],

          [R MTH/11/13  (Absolute)],

          [R MTH/12/13  (Absolute)],

          [R MTH/1/14  (Absolute)],

          [R MTH/2/14  (Absolute)],

          [R MTH/3/14  (Absolute)],

          [R MTH/4/14  (Absolute)],

          [R MTH/5/14  (Absolute)],

          [R MTH/6/14  (Absolute)],

          [R MTH/7/14  (Absolute)],

          [R MTH/8/14  (Absolute)],

          [R MTH/9/14  (Absolute)],

          [R MTH/10/14  (Absolute)],

          [R MTH/11/14  (Absolute)],

          [R MTH/12/14  (Absolute)],

          [R MTH/1/15  (Absolute)],

          [R MTH/2/15  (Absolute)],

          [R/HP MTH/3/10  (Absolute)],

          [R/HP MTH/4/10  (Absolute)],

          [R/HP MTH/5/10  (Absolute)],

          [R/HP MTH/6/10  (Absolute)],

          [R/HP MTH/7/10  (Absolute)],

          [R/HP MTH/8/10  (Absolute)],

          [R/HP MTH/9/10  (Absolute)],

          [R/HP MTH/10/10  (Absolute)],

          [R/HP MTH/11/10  (Absolute)],

          [R/HP MTH/12/10  (Absolute)],

          [R/HP MTH/1/11  (Absolute)],

          [R/HP MTH/2/11  (Absolute)],

          [R/HP MTH/3/11  (Absolute)],

          [R/HP MTH/4/11  (Absolute)],

          [R/HP MTH/5/11  (Absolute)],

          [R/HP MTH/6/11  (Absolute)],

          [R/HP MTH/7/11  (Absolute)],

          [R/HP MTH/8/11  (Absolute)],

          [R/HP MTH/9/11  (Absolute)],

          [R/HP MTH/10/11  (Absolute)],

          [R/HP MTH/11/11  (Absolute)],

          [R/HP MTH/12/11  (Absolute)],

          [R/HP MTH/1/12  (Absolute)],

          [R/HP MTH/2/12  (Absolute)],

          [R/HP MTH/3/12  (Absolute)],

          [R/HP MTH/4/12  (Absolute)],

          [R/HP MTH/5/12  (Absolute)],

          [R/HP MTH/6/12  (Absolute)],

          [R/HP MTH/7/12  (Absolute)],

          [R/HP MTH/8/12  (Absolute)],

          [R/HP MTH/9/12  (Absolute)],

          [R/HP MTH/10/12  (Absolute)],

          [R/HP MTH/11/12  (Absolute)],

          [R/HP MTH/12/12  (Absolute)],

          [R/HP MTH/1/13  (Absolute)],

          [R/HP MTH/2/13  (Absolute)],

          [R/HP MTH/3/13  (Absolute)],

          [R/HP MTH/4/13  (Absolute)],

          [R/HP MTH/5/13  (Absolute)],

          [R/HP MTH/6/13  (Absolute)],

          [R/HP MTH/7/13  (Absolute)],

          [R/HP MTH/8/13  (Absolute)],

          [R/HP MTH/9/13  (Absolute)],

          [R/HP MTH/10/13  (Absolute)],

          [R/HP MTH/11/13  (Absolute)],

          [R/HP MTH/12/13  (Absolute)],

          [R/HP MTH/1/14  (Absolute)],

          [R/HP MTH/2/14  (Absolute)],

          [R/HP MTH/3/14  (Absolute)],

          [R/HP MTH/4/14  (Absolute)],

          [R/HP MTH/5/14  (Absolute)],

          [R/HP MTH/6/14  (Absolute)],

          [R/HP MTH/7/14  (Absolute)],

          [R/HP MTH/8/14  (Absolute)],

          [R/HP MTH/9/14  (Absolute)],

          [R/HP MTH/10/14  (Absolute)],

          [R/HP MTH/11/14  (Absolute)],

          [R/HP MTH/12/14  (Absolute)],

          [R/HP MTH/1/15  (Absolute)],

          [R/HP MTH/2/15  (Absolute)],

        • Re: Loop through fields to change fieldname/data type
          Ali Hijazi

          you can rename a field by using Rename Field your_field to your_desired_field_name

          you can loop your fields using NoOfFileds function

          then using string function mid you can extract the year number and month number then using MonthName(makeDate(year,month),'YYYYMM') you can have your desired name format

           

          finally you can use generic load to make your columns (201003 ,....) as values in rows

           

          send me a sample QVW file to give you the complete syntax

          • Re: Loop through fields to change fieldname/data type
            Jonathan Dienst

            There are several steps required in your load script.

             

            • Assuming your data source has one or more key fields, the fields in your post and some values - crosstable load the data to get your field names as values (one key field, Item, in this example):

             

            T1:

            CrossTable(FName, Value, 1)

            LOAD * FROM [Sample.xlsx]

            (ooxml, embedded labels, table is Sheet1);

             

            • Now parse the field FName to get the Period and Measure and create a key that will be used later for the joins

             

            T2:

            LOAD *,

              AutoNumber(Item & Period) As RowID;

            LOAD Item,

              SubField(FName, ' ', 1) As Measure,

              Date(Date#(Mid(SubField(FName, ' ', 2), 5, 10), 'MM/YY'), 'YYYYMM') As Period,

              Value

            Resident T1;

            DROP Table T1;

             

            • Now do a generic load to get the measure as a field name, with the relevant values and the key field RowID for the joins:

             

            T3:

            Generic

            LOAD RowID, Measure, Value

            Resident T2;

             

            • The generic load spits out a bunch of tables, one per measure, named T3.<measure> (eg T3.UN) and containing the RowID and the value in a field named for the measure - eg [UN].
            • Combine these back with the next 2 steps. First load the 'framework' to which the generic tables will be joined:

             

            Final:

            LOAD Distinct Item,

              Period,

              RowID

            Resident T2;

             

            • Loop over the measures to join the generic-created tables to the framework and then drop these tables:

             

            For i = 1 To FieldValueCount('Measure');

              Let zFN = FieldValue('Measure', i);

              Left Join(Final) LOAD * Resident [T3.$(zFN)];

              DROP Table [T3.$(zFN)];

            Next

            DROP Table T2;

             

            Now you have Final containing fields: Item, Period, UN. KG, CU, R, R/HP (the last 5 containing the amounts from [Value]. I have assumed that is what you were trying to get.

             

            This approach is flexible in that if you were to remove or add measures, it would automatically adjust to the number of measures.

             

            HTH

            Jonathan

             

            PS: Sorry can't upload files from here

              • Re: Loop through fields to change fieldname/data type
                Jonathan Dienst

                As a CSV, the source file looks like this:

                 

                Item,UN MTH/3/10  (Absolute),UN MTH/4/10 (Absolute),UN MTH/5/10 (Absolute),UN MTH/6/10 (Absolute),UN MTH/7/10 (Absolute),UN MTH/8/10 (Absolute),UN MTH/9/10 (Absolute),UN MTH/10/10 (Absolute),UN MTH/11/10 (Absolute),UN MTH/12/10 (Absolute),UN MTH/1/11 (Absolute),UN MTH/2/11 (Absolute),UN MTH/3/11 (Absolute),UN MTH/4/11 (Absolute),UN MTH/5/11 (Absolute),UN MTH/6/11 (Absolute),UN MTH/7/11 (Absolute),UN MTH/8/11 (Absolute),UN MTH/9/11 (Absolute),UN MTH/10/11 (Absolute),UN MTH/11/11 (Absolute),UN MTH/12/11 (Absolute),UN MTH/1/12 (Absolute),UN MTH/2/12 (Absolute),UN MTH/3/12 (Absolute),UN MTH/4/12 (Absolute),UN MTH/5/12 (Absolute),UN MTH/6/12 (Absolute),UN MTH/7/12 (Absolute),UN MTH/8/12 (Absolute),UN MTH/9/12 (Absolute),UN MTH/10/12 (Absolute),UN MTH/11/12 (Absolute),UN MTH/12/12 (Absolute),UN MTH/1/13 (Absolute),UN MTH/2/13 (Absolute),UN MTH/3/13 (Absolute),UN MTH/4/13 (Absolute),UN MTH/5/13 (Absolute),UN MTH/6/13 (Absolute),UN MTH/7/13 (Absolute),UN MTH/8/13 (Absolute),UN MTH/9/13 (Absolute),UN MTH/10/13 (Absolute),UN MTH/11/13 (Absolute),UN MTH/12/13 (Absolute),UN MTH/1/14 (Absolute),UN MTH/2/14 (Absolute),UN MTH/3/14 (Absolute),UN MTH/4/14 (Absolute),UN MTH/5/14 (Absolute),UN MTH/6/14 (Absolute),UN MTH/7/14 (Absolute),UN MTH/8/14 (Absolute),UN MTH/9/14 (Absolute),UN MTH/10/14 (Absolute),UN MTH/11/14 (Absolute),UN MTH/12/14 (Absolute),UN MTH/1/15 (Absolute),UN MTH/2/15 (Absolute),KG MTH/3/10 (Absolute),KG MTH/4/10 (Absolute),KG MTH/5/10 (Absolute),KG MTH/6/10 (Absolute),KG MTH/7/10 (Absolute),KG MTH/8/10 (Absolute),KG MTH/9/10 (Absolute),KG MTH/10/10 (Absolute),KG MTH/11/10 (Absolute),KG MTH/12/10 (Absolute),KG MTH/1/11 (Absolute),KG MTH/2/11 (Absolute),KG MTH/3/11 (Absolute),KG MTH/4/11 (Absolute),KG MTH/5/11 (Absolute),KG MTH/6/11 (Absolute),KG MTH/7/11 (Absolute),KG MTH/8/11 (Absolute),KG MTH/9/11 (Absolute),KG MTH/10/11 (Absolute),KG MTH/11/11 (Absolute),KG MTH/12/11 (Absolute),KG MTH/1/12 (Absolute),KG MTH/2/12 (Absolute),KG MTH/3/12 (Absolute),KG MTH/4/12 (Absolute),KG MTH/5/12 (Absolute),KG MTH/6/12 (Absolute),KG MTH/7/12 (Absolute),KG MTH/8/12 (Absolute),KG MTH/9/12 (Absolute),KG MTH/10/12 (Absolute),KG MTH/11/12 (Absolute),KG MTH/12/12 (Absolute),KG MTH/1/13 (Absolute),KG MTH/2/13 (Absolute),KG MTH/3/13 (Absolute),KG MTH/4/13 (Absolute),KG MTH/5/13 (Absolute),KG MTH/6/13 (Absolute),KG MTH/7/13 (Absolute),KG MTH/8/13 (Absolute),KG MTH/9/13 (Absolute),KG MTH/10/13 (Absolute),KG MTH/11/13 (Absolute),KG MTH/12/13 (Absolute),KG MTH/1/14 (Absolute),KG MTH/2/14 (Absolute),KG MTH/3/14 (Absolute),KG MTH/4/14 (Absolute),KG MTH/5/14 (Absolute),KG MTH/6/14 (Absolute),KG MTH/7/14 (Absolute),KG MTH/8/14 (Absolute),KG MTH/9/14 (Absolute),KG MTH/10/14 (Absolute),KG MTH/11/14 (Absolute),KG MTH/12/14 (Absolute),KG MTH/1/15 (Absolute),KG MTH/2/15 (Absolute),CU MTH/3/10 (Absolute),CU MTH/4/10 (Absolute),CU MTH/5/10 (Absolute),CU MTH/6/10 (Absolute),CU MTH/7/10 (Absolute),CU MTH/8/10 (Absolute),CU MTH/9/10 (Absolute),CU MTH/10/10 (Absolute),CU MTH/11/10 (Absolute),CU MTH/12/10 (Absolute),CU MTH/1/11 (Absolute),CU MTH/2/11 (Absolute),CU MTH/3/11 (Absolute),CU MTH/4/11 (Absolute),CU MTH/5/11 (Absolute),CU MTH/6/11 (Absolute),CU MTH/7/11 (Absolute),CU MTH/8/11 (Absolute),CU MTH/9/11 (Absolute),CU MTH/10/11 (Absolute),CU MTH/11/11 (Absolute),CU MTH/12/11 (Absolute),CU MTH/1/12 (Absolute),CU MTH/2/12 (Absolute),CU MTH/3/12 (Absolute),CU MTH/4/12 (Absolute),CU MTH/5/12 (Absolute),CU MTH/6/12 (Absolute),CU MTH/7/12 (Absolute),CU MTH/8/12 (Absolute),CU MTH/9/12 (Absolute),CU MTH/10/12 (Absolute),CU MTH/11/12 (Absolute),CU MTH/12/12 (Absolute),CU MTH/1/13 (Absolute),CU MTH/2/13 (Absolute),CU MTH/3/13 (Absolute),CU MTH/4/13 (Absolute),CU MTH/5/13 (Absolute),CU MTH/6/13 (Absolute),CU MTH/7/13 (Absolute),CU MTH/8/13 (Absolute),CU MTH/9/13 (Absolute),CU MTH/10/13 (Absolute),CU MTH/11/13 (Absolute),CU MTH/12/13 (Absolute),CU MTH/1/14 (Absolute),CU MTH/2/14 (Absolute),CU MTH/3/14 (Absolute),CU MTH/4/14 (Absolute),CU MTH/5/14 (Absolute),CU MTH/6/14 (Absolute),CU MTH/7/14 (Absolute),CU MTH/8/14 (Absolute),CU MTH/9/14 (Absolute),CU MTH/10/14 (Absolute),CU MTH/11/14 (Absolute),CU MTH/12/14 (Absolute),CU MTH/1/15 (Absolute),CU MTH/2/15 (Absolute),R MTH/3/10 (Absolute),R MTH/4/10 (Absolute),R MTH/5/10 (Absolute),R MTH/6/10 (Absolute),R MTH/7/10 (Absolute),R MTH/8/10 (Absolute),R MTH/9/10 (Absolute),R MTH/10/10 (Absolute),R MTH/11/10 (Absolute),R MTH/12/10 (Absolute),R MTH/1/11 (Absolute),R MTH/2/11 (Absolute),R MTH/3/11 (Absolute),R MTH/4/11 (Absolute),R MTH/5/11 (Absolute),R MTH/6/11 (Absolute),R MTH/7/11 (Absolute),R MTH/8/11 (Absolute),R MTH/9/11 (Absolute),R MTH/10/11 (Absolute),R MTH/11/11 (Absolute),R MTH/12/11 (Absolute),R MTH/1/12 (Absolute),R MTH/2/12 (Absolute),R MTH/3/12 (Absolute),R MTH/4/12 (Absolute),R MTH/5/12 (Absolute),R MTH/6/12 (Absolute),R MTH/7/12 (Absolute),R MTH/8/12 (Absolute),R MTH/9/12 (Absolute),R MTH/10/12 (Absolute),R MTH/11/12 (Absolute),R MTH/12/12 (Absolute),R MTH/1/13 (Absolute),R MTH/2/13 (Absolute),R MTH/3/13 (Absolute),R MTH/4/13 (Absolute),R MTH/5/13 (Absolute),R MTH/6/13 (Absolute),R MTH/7/13 (Absolute),R MTH/8/13 (Absolute),R MTH/9/13 (Absolute),R MTH/10/13 (Absolute),R MTH/11/13 (Absolute),R MTH/12/13 (Absolute),R MTH/1/14 (Absolute),R MTH/2/14 (Absolute),R MTH/3/14 (Absolute),R MTH/4/14 (Absolute),R MTH/5/14 (Absolute),R MTH/6/14 (Absolute),R MTH/7/14 (Absolute),R MTH/8/14 (Absolute),R MTH/9/14 (Absolute),R MTH/10/14 (Absolute),R MTH/11/14 (Absolute),R MTH/12/14 (Absolute),R MTH/1/15 (Absolute),R MTH/2/15 (Absolute),R/HP MTH/3/10 (Absolute),R/HP MTH/4/10 (Absolute),R/HP MTH/5/10 (Absolute),R/HP MTH/6/10 (Absolute),R/HP MTH/7/10 (Absolute),R/HP MTH/8/10 (Absolute),R/HP MTH/9/10 (Absolute),R/HP MTH/10/10 (Absolute),R/HP MTH/11/10 (Absolute),R/HP MTH/12/10 (Absolute),R/HP MTH/1/11 (Absolute),R/HP MTH/2/11 (Absolute),R/HP MTH/3/11 (Absolute),R/HP MTH/4/11 (Absolute),R/HP MTH/5/11 (Absolute),R/HP MTH/6/11 (Absolute),R/HP MTH/7/11 (Absolute),R/HP MTH/8/11 (Absolute),R/HP MTH/9/11 (Absolute),R/HP MTH/10/11 (Absolute),R/HP MTH/11/11 (Absolute),R/HP MTH/12/11 (Absolute),R/HP MTH/1/12 (Absolute),R/HP MTH/2/12 (Absolute),R/HP MTH/3/12 (Absolute),R/HP MTH/4/12 (Absolute),R/HP MTH/5/12 (Absolute),R/HP MTH/6/12 (Absolute),R/HP MTH/7/12 (Absolute),R/HP MTH/8/12 (Absolute),R/HP MTH/9/12 (Absolute),R/HP MTH/10/12 (Absolute),R/HP MTH/11/12 (Absolute),R/HP MTH/12/12 (Absolute),R/HP MTH/1/13 (Absolute),R/HP MTH/2/13 (Absolute),R/HP MTH/3/13 (Absolute),R/HP MTH/4/13 (Absolute),R/HP MTH/5/13 (Absolute),R/HP MTH/6/13 (Absolute),R/HP MTH/7/13 (Absolute),R/HP MTH/8/13 (Absolute),R/HP MTH/9/13 (Absolute),R/HP MTH/10/13 (Absolute),R/HP MTH/11/13 (Absolute),R/HP MTH/12/13 (Absolute),R/HP MTH/1/14 (Absolute),R/HP MTH/2/14 (Absolute),R/HP MTH/3/14 (Absolute),R/HP MTH/4/14 (Absolute),R/HP MTH/5/14 (Absolute),R/HP MTH/6/14 (Absolute),R/HP MTH/7/14 (Absolute),R/HP MTH/8/14 (Absolute),R/HP MTH/9/14 (Absolute),R/HP MTH/10/14 (Absolute),R/HP MTH/11/14 (Absolute),R/HP MTH/12/14 (Absolute),R/HP MTH/1/15 (Absolute),R/HP MTH/2/15 (Absolute)

                A,95,62,46,32,4,59,84,48,64,66,75,31,92,90,70,36,71,49,59,75,62,10,47,89,55,16,75,14,81,53,19,27,82,84,73,5,19,52,65,22,87,34,13,87,68,99,96,27,79,81,16,58,96,29,74,55,31,88,18,51,5,53,34,41,70,15,24,21,8,41,59,59,25,35,60,22,29,37,19,100,81,68,98,42,7,93,89,24,85,9,37,84,58,0,53,86,80,42,40,42,60,3,87,37,51,49,76,46,2,69,41,22,13,87,32,13,15,78,91,33,64,19,44,57,17,28,59,1,43,48,66,72,23,75,36,82,9,36,17,48,55,14,4,28,48,40,60,5,94,76,84,71,75,45,26,49,99,28,35,77,38,34,78,12,72,39,6,5,44,1,34,31,95,6,34,55,79,42,52,21,19,7,0,26,51,93,87,45,7,65,80,45,91,46,2,89,69,80,15,88,3,45,15,13,28,32,32,55,20,97,36,61,54,12,14,23,30,17,48,32,48,75,64,68,55,79,26,61,99,76,48,11,90,89,17,98,81,72,18,41,69,29,95,63,58,76,77,98,22,11,51,100,76,75,93,30,67,68,82,57,72,30,88,98,99,13,19,73,28,6,45,23,92,5,26,46,32,82,19,58,56,81,78,13,33,35,81,92,30,23,61,98,71,28,83,63,72,90,29,74

                B,7,92,61,26,15,78,47,42,40,74,35,32,76,94,100,88,28,87,12,76,84,39,4,54,61,31,76,3,79,18,15,83,70,57,10,96,51,85,20,64,40,74,3,60,20,44,39,17,31,35,89,97,71,93,53,11,45,60,29,86,6,37,51,80,35,10,46,15,30,5,91,86,98,48,46,65,31,92,19,34,48,6,46,77,40,23,36,8,95,97,92,96,43,82,39,9,42,85,98,59,60,83,59,20,35,68,49,34,52,29,25,17,27,56,45,38,16,14,39,54,75,64,41,69,3,22,2,69,78,89,97,29,43,16,63,49,11,47,27,22,64,99,7,17,54,11,29,0,82,91,15,70,40,20,35,99,99,97,57,25,75,70,5,27,79,99,70,65,93,47,92,21,51,1,71,21,43,6,45,70,4,5,30,35,64,55,57,25,75,78,60,87,61,88,42,53,37,42,35,25,91,1,29,6,25,72,41,98,33,42,42,41,72,94,85,95,88,64,85,65,50,30,97,19,60,0,73,26,43,7,24,54,68,61,10,73,77,65,99,5,36,57,88,39,6,50,55,0,74,53,40,78,50,48,95,88,30,65,30,61,10,14,18,83,20,74,52,78,5,90,89,66,22,76,93,48,35,90,92,71,46,2,72,74,82,57,68,32,66,10,29,53,13,71,82,66,44,44,27,3

                C,12,18,94,16,26,19,33,10,54,94,54,21,29,83,60,60,38,46,25,59,46,27,1,52,10,65,2,67,100,14,2,21,27,44,26,19,37,49,4,15,76,61,60,5,52,83,60,49,72,75,82,71,8,31,29,39,57,69,29,53,85,22,50,45,95,21,23,32,27,27,68,69,98,2,99,37,97,78,28,38,76,33,26,53,80,55,66,37,62,16,25,70,76,5,94,31,67,49,41,25,38,98,58,10,9,62,28,73,36,59,56,16,74,43,95,81,86,19,83,18,78,81,85,8,95,13,74,78,35,80,45,30,3,36,56,31,3,74,7,61,30,30,45,20,35,71,77,29,29,55,38,9,85,48,50,36,60,52,52,53,92,37,20,81,71,2,31,100,12,73,54,26,59,35,93,61,39,57,46,52,25,14,81,98,7,81,100,34,67,75,29,20,64,57,20,2,53,54,68,40,63,5,9,63,71,97,100,48,21,39,56,48,41,10,9,48,74,77,41,32,30,54,33,92,71,77,78,20,39,64,2,64,79,23,19,30,63,76,36,6,97,5,3,67,22,81,83,31,9,63,55,44,87,24,98,27,37,63,96,17,31,85,15,32,63,32,40,38,69,25,37,3,42,8,8,99,84,4,73,75,15,46,18,28,91,17,70,31,24,16,11,5,2,84,62,18,8,41,72,10

                • Re: Loop through fields to change fieldname/data type
                  chris goudy

                  Hi Jonathan,

                   

                  Thanks for your help.  I'm a little unclear about which fields to use where, perhaps because i did not list all the fields i am loading in the script for this table. Would you please provide further clarification? Thanks so much. 

                   

                  DataviewValues:

                  LOAD

                    Product&'|'&Pack as ProductKey,

                    Product,

                       Pack,

                       [UN MTH/3/10  (Absolute)],

                       [UN MTH/4/10  (Absolute)],

                       [UN MTH/5/10  (Absolute)],

                       [UN MTH/6/10  (Absolute)],

                       [UN MTH/7/10  (Absolute)],

                       [UN MTH/8/10  (Absolute)],

                       [UN MTH/9/10  (Absolute)],

                       [UN MTH/10/10  (Absolute)],

                       [UN MTH/11/10  (Absolute)],

                       [UN MTH/12/10  (Absolute)],

                       [UN MTH/1/11  (Absolute)],

                       [UN MTH/2/11  (Absolute)],

                       [UN MTH/3/11  (Absolute)],

                       [UN MTH/4/11  (Absolute)],

                       [UN MTH/5/11  (Absolute)],

                       [UN MTH/6/11  (Absolute)],

                       [UN MTH/7/11  (Absolute)],

                       [UN MTH/8/11  (Absolute)],

                       [UN MTH/9/11  (Absolute)],

                       [UN MTH/10/11  (Absolute)],

                       [UN MTH/11/11  (Absolute)],

                       [UN MTH/12/11  (Absolute)],

                       [UN MTH/1/12  (Absolute)],

                       [UN MTH/2/12  (Absolute)],

                       [UN MTH/3/12  (Absolute)],

                       [UN MTH/4/12  (Absolute)],

                       [UN MTH/5/12  (Absolute)],

                       [UN MTH/6/12  (Absolute)],

                       [UN MTH/7/12  (Absolute)],

                       [UN MTH/8/12  (Absolute)],

                       [UN MTH/9/12  (Absolute)],

                       [UN MTH/10/12  (Absolute)],

                       [UN MTH/11/12  (Absolute)],

                       [UN MTH/12/12  (Absolute)],

                       [UN MTH/1/13  (Absolute)],

                       [UN MTH/2/13  (Absolute)],

                       [UN MTH/3/13  (Absolute)],

                       [UN MTH/4/13  (Absolute)],

                       [UN MTH/5/13  (Absolute)],

                       [UN MTH/6/13  (Absolute)],

                       [UN MTH/7/13  (Absolute)],

                       [UN MTH/8/13  (Absolute)],

                       [UN MTH/9/13  (Absolute)],

                       [UN MTH/10/13  (Absolute)],

                       [UN MTH/11/13  (Absolute)],

                       [UN MTH/12/13  (Absolute)],

                       [UN MTH/1/14  (Absolute)],

                       [UN MTH/2/14  (Absolute)],

                       [UN MTH/3/14  (Absolute)],

                       [UN MTH/4/14  (Absolute)],

                       [UN MTH/5/14  (Absolute)],

                       [UN MTH/6/14  (Absolute)],

                       [UN MTH/7/14  (Absolute)],

                       [UN MTH/8/14  (Absolute)],

                       [UN MTH/9/14  (Absolute)],

                       [UN MTH/10/14  (Absolute)],

                       [UN MTH/11/14  (Absolute)],

                       [UN MTH/12/14  (Absolute)],

                       [UN MTH/1/15  (Absolute)],

                       [UN MTH/2/15  (Absolute)]

                       [KG MTH/3/10  (Absolute)],

                       [KG MTH/4/10  (Absolute)],

                       [KG MTH/5/10  (Absolute)],

                       [KG MTH/6/10  (Absolute)],

                       [KG MTH/7/10  (Absolute)],

                       [KG MTH/8/10  (Absolute)],

                       [KG MTH/9/10  (Absolute)],

                       [KG MTH/10/10  (Absolute)],

                       [KG MTH/11/10  (Absolute)],

                       [KG MTH/12/10  (Absolute)],

                       [KG MTH/1/11  (Absolute)],

                       [KG MTH/2/11  (Absolute)],

                       [KG MTH/3/11  (Absolute)],

                       [KG MTH/4/11  (Absolute)],

                       [KG MTH/5/11  (Absolute)],

                       [KG MTH/6/11  (Absolute)],

                       [KG MTH/7/11  (Absolute)],

                       [KG MTH/8/11  (Absolute)],

                       [KG MTH/9/11  (Absolute)],

                       [KG MTH/10/11  (Absolute)],

                       [KG MTH/11/11  (Absolute)],

                       [KG MTH/12/11  (Absolute)],

                       [KG MTH/1/12  (Absolute)],

                       [KG MTH/2/12  (Absolute)],

                       [KG MTH/3/12  (Absolute)],

                       [KG MTH/4/12  (Absolute)],

                       [KG MTH/5/12  (Absolute)],

                       [KG MTH/6/12  (Absolute)],

                       [KG MTH/7/12  (Absolute)],

                       [KG MTH/8/12  (Absolute)],

                       [KG MTH/9/12  (Absolute)],

                       [KG MTH/10/12  (Absolute)],

                       [KG MTH/11/12  (Absolute)],

                       [KG MTH/12/12  (Absolute)],

                       [KG MTH/1/13  (Absolute)],

                       [KG MTH/2/13  (Absolute)],

                       [KG MTH/3/13  (Absolute)],

                       [KG MTH/4/13  (Absolute)],

                       [KG MTH/5/13  (Absolute)],

                       [KG MTH/6/13  (Absolute)],

                       [KG MTH/7/13  (Absolute)],

                       [KG MTH/8/13  (Absolute)],

                       [KG MTH/9/13  (Absolute)],

                       [KG MTH/10/13  (Absolute)],

                       [KG MTH/11/13  (Absolute)],

                       [KG MTH/12/13  (Absolute)],

                       [KG MTH/1/14  (Absolute)],

                       [KG MTH/2/14  (Absolute)],

                       [KG MTH/3/14  (Absolute)],

                       [KG MTH/4/14  (Absolute)],

                       [KG MTH/5/14  (Absolute)],

                       [KG MTH/6/14  (Absolute)],

                       [KG MTH/7/14  (Absolute)],

                       [KG MTH/8/14  (Absolute)],

                       [KG MTH/9/14  (Absolute)],

                       [KG MTH/10/14  (Absolute)],

                       [KG MTH/11/14  (Absolute)],

                       [KG MTH/12/14  (Absolute)],

                       [KG MTH/1/15  (Absolute)],

                       [KG MTH/2/15  (Absolute)],

                       [CU MTH/3/10  (Absolute)],

                       [CU MTH/4/10  (Absolute)],

                       [CU MTH/5/10  (Absolute)],

                       [CU MTH/6/10  (Absolute)],

                       [CU MTH/7/10  (Absolute)],

                       [CU MTH/8/10  (Absolute)],

                       [CU MTH/9/10  (Absolute)],

                       [CU MTH/10/10  (Absolute)],

                       [CU MTH/11/10  (Absolute)],

                       [CU MTH/12/10  (Absolute)],

                       [CU MTH/1/11  (Absolute)],

                       [CU MTH/2/11  (Absolute)],

                       [CU MTH/3/11  (Absolute)],

                       [CU MTH/4/11  (Absolute)],

                       [CU MTH/5/11  (Absolute)],

                       [CU MTH/6/11  (Absolute)],

                       [CU MTH/7/11  (Absolute)],

                       [CU MTH/8/11  (Absolute)],

                       [CU MTH/9/11  (Absolute)],

                       [CU MTH/10/11  (Absolute)],

                       [CU MTH/11/11  (Absolute)],

                       [CU MTH/12/11  (Absolute)],

                       [CU MTH/1/12  (Absolute)],

                       [CU MTH/2/12  (Absolute)],

                       [CU MTH/3/12  (Absolute)],

                       [CU MTH/4/12  (Absolute)],

                       [CU MTH/5/12  (Absolute)],

                       [CU MTH/6/12  (Absolute)],

                       [CU MTH/7/12  (Absolute)],

                       [CU MTH/8/12  (Absolute)],

                       [CU MTH/9/12  (Absolute)],

                       [CU MTH/10/12  (Absolute)],

                       [CU MTH/11/12  (Absolute)],

                       [CU MTH/12/12  (Absolute)],

                       [CU MTH/1/13  (Absolute)],

                       [CU MTH/2/13  (Absolute)],

                       [CU MTH/3/13  (Absolute)],

                       [CU MTH/4/13  (Absolute)],

                       [CU MTH/5/13  (Absolute)],

                       [CU MTH/6/13  (Absolute)],

                       [CU MTH/7/13  (Absolute)],

                       [CU MTH/8/13  (Absolute)],

                       [CU MTH/9/13  (Absolute)],

                       [CU MTH/10/13  (Absolute)],

                       [CU MTH/11/13  (Absolute)],

                       [CU MTH/12/13  (Absolute)],

                       [CU MTH/1/14  (Absolute)],

                       [CU MTH/2/14  (Absolute)],

                       [CU MTH/3/14  (Absolute)],

                       [CU MTH/4/14  (Absolute)],

                       [CU MTH/5/14  (Absolute)],

                       [CU MTH/6/14  (Absolute)],

                       [CU MTH/7/14  (Absolute)],

                       [CU MTH/8/14  (Absolute)],

                       [CU MTH/9/14  (Absolute)],

                       [CU MTH/10/14  (Absolute)],

                       [CU MTH/11/14  (Absolute)],

                       [CU MTH/12/14  (Absolute)],

                       [CU MTH/1/15  (Absolute)],

                       [CU MTH/2/15  (Absolute)],

                       [R MTH/3/10  (Absolute)],

                       [R MTH/4/10  (Absolute)],

                       [R MTH/5/10  (Absolute)],

                       [R MTH/6/10  (Absolute)],

                       [R MTH/7/10  (Absolute)],

                       [R MTH/8/10  (Absolute)],

                       [R MTH/9/10  (Absolute)],

                       [R MTH/10/10  (Absolute)],

                       [R MTH/11/10  (Absolute)],

                       [R MTH/12/10  (Absolute)],

                       [R MTH/1/11  (Absolute)],

                       [R MTH/2/11  (Absolute)],

                       [R MTH/3/11  (Absolute)],

                       [R MTH/4/11  (Absolute)],

                       [R MTH/5/11  (Absolute)],

                       [R MTH/6/11  (Absolute)],

                       [R MTH/7/11  (Absolute)],

                       [R MTH/8/11  (Absolute)],

                       [R MTH/9/11  (Absolute)],

                       [R MTH/10/11  (Absolute)],

                       [R MTH/11/11  (Absolute)],

                       [R MTH/12/11  (Absolute)],

                       [R MTH/1/12  (Absolute)],

                       [R MTH/2/12  (Absolute)],

                       [R MTH/3/12  (Absolute)],

                       [R MTH/4/12  (Absolute)],

                       [R MTH/5/12  (Absolute)],

                       [R MTH/6/12  (Absolute)],

                       [R MTH/7/12  (Absolute)],

                       [R MTH/8/12  (Absolute)],

                       [R MTH/9/12  (Absolute)],

                       [R MTH/10/12  (Absolute)],

                       [R MTH/11/12  (Absolute)],

                       [R MTH/12/12  (Absolute)],

                       [R MTH/1/13  (Absolute)],

                       [R MTH/2/13  (Absolute)],

                       [R MTH/3/13  (Absolute)],

                       [R MTH/4/13  (Absolute)],

                       [R MTH/5/13  (Absolute)],

                       [R MTH/6/13  (Absolute)],

                       [R MTH/7/13  (Absolute)],

                       [R MTH/8/13  (Absolute)],

                       [R MTH/9/13  (Absolute)],

                       [R MTH/10/13  (Absolute)],

                       [R MTH/11/13  (Absolute)],

                       [R MTH/12/13  (Absolute)],

                       [R MTH/1/14  (Absolute)],

                       [R MTH/2/14  (Absolute)],

                       [R MTH/3/14  (Absolute)],

                       [R MTH/4/14  (Absolute)],

                       [R MTH/5/14  (Absolute)],

                       [R MTH/6/14  (Absolute)],

                       [R MTH/7/14  (Absolute)],

                       [R MTH/8/14  (Absolute)],

                       [R MTH/9/14  (Absolute)],

                       [R MTH/10/14  (Absolute)],

                       [R MTH/11/14  (Absolute)],

                       [R MTH/12/14  (Absolute)],

                       [R MTH/1/15  (Absolute)],

                       [R MTH/2/15  (Absolute)],

                       [R/HP MTH/3/10  (Absolute)],

                       [R/HP MTH/4/10  (Absolute)],

                       [R/HP MTH/5/10  (Absolute)],

                       [R/HP MTH/6/10  (Absolute)],

                       [R/HP MTH/7/10  (Absolute)],

                       [R/HP MTH/8/10  (Absolute)],

                       [R/HP MTH/9/10  (Absolute)],

                       [R/HP MTH/10/10  (Absolute)],

                       [R/HP MTH/11/10  (Absolute)],

                       [R/HP MTH/12/10  (Absolute)],

                       [R/HP MTH/1/11  (Absolute)],

                       [R/HP MTH/2/11  (Absolute)],

                       [R/HP MTH/3/11  (Absolute)],

                       [R/HP MTH/4/11  (Absolute)],

                       [R/HP MTH/5/11  (Absolute)],

                       [R/HP MTH/6/11  (Absolute)],

                       [R/HP MTH/7/11  (Absolute)],

                       [R/HP MTH/8/11  (Absolute)],

                       [R/HP MTH/9/11  (Absolute)],

                       [R/HP MTH/10/11  (Absolute)],

                       [R/HP MTH/11/11  (Absolute)],

                       [R/HP MTH/12/11  (Absolute)],

                       [R/HP MTH/1/12  (Absolute)],

                       [R/HP MTH/2/12  (Absolute)],

                       [R/HP MTH/3/12  (Absolute)],

                       [R/HP MTH/4/12  (Absolute)],

                       [R/HP MTH/5/12  (Absolute)],

                       [R/HP MTH/6/12  (Absolute)],

                       [R/HP MTH/7/12  (Absolute)],

                       [R/HP MTH/8/12  (Absolute)],

                       [R/HP MTH/9/12  (Absolute)],

                       [R/HP MTH/10/12  (Absolute)],

                       [R/HP MTH/11/12  (Absolute)],

                       [R/HP MTH/12/12  (Absolute)],

                       [R/HP MTH/1/13  (Absolute)],

                       [R/HP MTH/2/13  (Absolute)],

                       [R/HP MTH/3/13  (Absolute)],

                       [R/HP MTH/4/13  (Absolute)],

                       [R/HP MTH/5/13  (Absolute)],

                       [R/HP MTH/6/13  (Absolute)],

                       [R/HP MTH/7/13  (Absolute)],

                       [R/HP MTH/8/13  (Absolute)],

                       [R/HP MTH/9/13  (Absolute)],

                       [R/HP MTH/10/13  (Absolute)],

                       [R/HP MTH/11/13  (Absolute)],

                       [R/HP MTH/12/13  (Absolute)],

                       [R/HP MTH/1/14  (Absolute)],

                       [R/HP MTH/2/14  (Absolute)],

                       [R/HP MTH/3/14  (Absolute)],

                       [R/HP MTH/4/14  (Absolute)],

                       [R/HP MTH/5/14  (Absolute)],

                       [R/HP MTH/6/14  (Absolute)],

                       [R/HP MTH/7/14  (Absolute)],

                       [R/HP MTH/8/14  (Absolute)],

                       [R/HP MTH/9/14  (Absolute)],

                       [R/HP MTH/10/14  (Absolute)],

                       [R/HP MTH/11/14  (Absolute)],

                       [R/HP MTH/12/14  (Absolute)],

                       [R/HP MTH/1/15  (Absolute)],

                       [R/HP MTH/2/15  (Absolute)],

                       [IU MTH/3/10  (Absolute)],

                       [IU MTH/4/10  (Absolute)],

                       [IU MTH/5/10  (Absolute)],

                       [IU MTH/6/10  (Absolute)],

                       [IU MTH/7/10  (Absolute)],

                       [IU MTH/8/10  (Absolute)],

                       [IU MTH/9/10  (Absolute)],

                       [IU MTH/10/10  (Absolute)],

                       [IU MTH/11/10  (Absolute)],

                       [IU MTH/12/10  (Absolute)],

                       [IU MTH/1/11  (Absolute)],

                       [IU MTH/2/11  (Absolute)],

                       [IU MTH/3/11  (Absolute)],

                       [IU MTH/4/11  (Absolute)],

                       [IU MTH/5/11  (Absolute)],

                       [IU MTH/6/11  (Absolute)],

                       [IU MTH/7/11  (Absolute)],

                       [IU MTH/8/11  (Absolute)],

                       [IU MTH/9/11  (Absolute)],

                       [IU MTH/10/11  (Absolute)],

                       [IU MTH/11/11  (Absolute)],

                       [IU MTH/12/11  (Absolute)],

                       [IU MTH/1/12  (Absolute)],

                       [IU MTH/2/12  (Absolute)],

                       [IU MTH/3/12  (Absolute)],

                       [IU MTH/4/12  (Absolute)],

                       [IU MTH/5/12  (Absolute)],

                       [IU MTH/6/12  (Absolute)],

                       [IU MTH/7/12  (Absolute)],

                       [IU MTH/8/12  (Absolute)],

                       [IU MTH/9/12  (Absolute)],

                       [IU MTH/10/12  (Absolute)],

                       [IU MTH/11/12  (Absolute)],

                       [IU MTH/12/12  (Absolute)],

                       [IU MTH/1/13  (Absolute)],

                       [IU MTH/2/13  (Absolute)],

                       [IU MTH/3/13  (Absolute)],

                       [IU MTH/4/13  (Absolute)],

                       [IU MTH/5/13  (Absolute)],

                       [IU MTH/6/13  (Absolute)],

                       [IU MTH/7/13  (Absolute)],

                       [IU MTH/8/13  (Absolute)],

                       [IU MTH/9/13  (Absolute)],

                       [IU MTH/10/13  (Absolute)],

                       [IU MTH/11/13  (Absolute)],

                       [IU MTH/12/13  (Absolute)],

                       [IU MTH/1/14  (Absolute)],

                       [IU MTH/2/14  (Absolute)],

                       [IU MTH/3/14  (Absolute)],

                       [IU MTH/4/14  (Absolute)],

                       [IU MTH/5/14  (Absolute)],

                       [IU MTH/6/14  (Absolute)],

                       [IU MTH/7/14  (Absolute)],

                       [IU MTH/8/14  (Absolute)],

                       [IU MTH/9/14  (Absolute)],

                       [IU MTH/10/14  (Absolute)],

                       [IU MTH/11/14  (Absolute)],

                       [IU MTH/12/14  (Absolute)],

                       [IU MTH/1/15  (Absolute)],

                       [IU MTH/2/15  (Absolute)]

                   

                  FROM

                  [Values.csv]

                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                  ;

                    • Re: Loop through fields to change fieldname/data type
                      Jonathan Dienst

                      You could load from that table with:

                       

                      T1:

                      CrossTable(FName, Value, 3)

                      LOAD * Resident DataviewValues;

                       

                      T2:

                      LOAD ProductKey,

                        AutoNumber(ProductKey) As RowID,

                        Product,

                        Pack,

                        SubField(FName, ' ', 1) As Measure,

                        Date(Date#(Mid(SubField(FName, ' ', 2), 5, 10), 'MM/YY'), 'YYYYMM') As Period,

                        Value

                      Resident T1;

                      DROP Table T1;

                       

                      T3:

                      Generic

                      LOAD RowID, Measure, Value

                      Resident T2;

                       

                      Final:

                      LOAD Distinct

                        RowID,

                        ProductKey,

                        Product,

                        Pack,

                        Period

                      Resident T2;

                       

                      For i = 1 To FieldValueCount('Measure');

                        Let zFN = FieldValue('Measure', i);

                        Left Join(Final) LOAD * Resident [T3.$(zFN)];

                        DROP Table [T3.$(zFN)];

                      Next

                       

                      DROP Table T2;

                      DROP Table DataviewValues;  // if you dont need this anymore

                       

                       

                      HTH

                      Jonathan

                        • Re: Loop through fields to change fieldname/data type
                          chris goudy

                          Hi Jonathan,

                           

                          Thanks for your help with this. I'm having some trouble once the Generic Load has been run.

                          When i select an individual Measure (R) and ScriptMonth (201502), it has 60 associated R values (see table below). The Value field is the initial and correct value for the ScriptMonth shown. 

                           

                          It looks like the value for each ScriptMonth is being linked to every ScriptMonth.  (5 years x 12 months = 60).

                           

                          Is there something I can do to fix this problem? I've included the script I've used beow. Please let me know if you need any further information from me. Thanks so much

                              

                          RMeasureScriptMonthValue
                          6,460,469R2015027,797,743
                          7,275,035R2015027,797,743
                          7,303,479R2015027,797,743
                          7,667,890R2015027,797,743
                          7,797,743R2015027,797,743
                          8,912,000R2015027,797,743
                          9,452,846R2015027,797,743
                          10,236,908R2015027,797,743
                          11,252,744R2015027,797,743
                          11,266,565R2015027,797,743
                          11,859,432R2015027,797,743
                          11,883,223R2015027,797,743
                          12,005,541R2015027,797,743
                          12,064,184R2015027,797,743
                          12,126,363R2015027,797,743
                          12,695,024R2015027,797,743
                          12,697,908R2015027,797,743
                          12,784,784R2015027,797,743
                          12,861,566R2015027,797,743
                          12,995,778R2015027,797,743
                          13,041,445R2015027,797,743
                          13,184,352R2015027,797,743
                          13,232,500R2015027,797,743
                          13,335,597R2015027,797,743
                          13,371,092R2015027,797,743
                          13,420,965R2015027,797,743
                          13,533,861R2015027,797,743
                          13,608,125R2015027,797,743
                          13,632,633R2015027,797,743
                          13,646,716R2015027,797,743
                          13,705,450R2015027,797,743
                          13,776,464R2015027,797,743
                          13,889,294R2015027,797,743
                          14,028,052R2015027,797,743
                          14,078,907R2015027,797,743
                          14,099,373R2015027,797,743
                          14,148,480R2015027,797,743
                          14,238,662R2015027,797,743
                          14,257,809R2015027,797,743
                          14,268,827R2015027,797,743
                          14,369,741R2015027,797,743
                          14,412,821R2015027,797,743
                          14,515,549R2015027,797,743
                          14,519,774R2015027,797,743
                          14,635,394R2015027,797,743
                          14,726,895R2015027,797,743
                          14,816,809R2015027,797,743
                          14,986,845R2015027,797,743
                          15,030,574R2015027,797,743
                          15,138,692R2015027,797,743
                          15,354,646R2015027,797,743
                          15,483,054R2015027,797,743
                          15,536,567R2015027,797,743
                          15,588,115R2015027,797,743
                          15,933,855R2015027,797,743
                          15,968,044R2015027,797,743
                          16,112,064R2015027,797,743
                          16,282,021R2015027,797,743
                          16,426,544R2015027,797,743
                          19,933,662R2015027,797,743

                           

                          //SCRIPT USED:

                           

                          DataviewValues1:

                          CrossTable(ScriptMonth_Measure, Value, 2)

                          LOAD *

                          FROM

                          [C:\Users\chrisg\Desktop\TPM\QlikViews\Test2\DataviewValues.csv]
                          (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

                          ;

                           

                          //////
                          //Parse the field ScriptMonth to get the Period and Measure and create a key that will be used later for the joins
                          //////

                           

                          T1:

                          LOAD

                          Product&'|'&Pack  as ProductKey
                          ,Product
                          ,Pack
                          ,AutoNumber(Product&'|'&Pack) As RowID
                          ,ScriptMonth_Measure
                          ,SubField(ScriptMonth_Measure, ' ', 1) As Measure
                          ,Date(Date#(Mid(SubField(ScriptMonth_Measure, ' ', 2), 5, 10), 'MM/YY'), 'YYYYMM') As ScriptMonth
                          ,Value

                          Resident DataviewValues1

                          WHERE Value>1

                          ;

                           

                          ////generic load to get the measure as a field name, with the relevant values and the key field RowID for the joins:

                           

                          T3:

                          Generic

                          LOAD RowID, Measure, Value

                          Resident T1

                          //WHERE Value > 0

                          ;

                           

                          ////The generic load spits out a bunch of tables, one per measure, named T3.<measure> (eg T3.UN) and containing the RowID and the value in a field named for the measure - eg [UN].

                          ////Combine these back with the next 2 steps. First load the 'framework' to which the generic tables will be joined:

                           

                          Final:

                          LOAD Distinct

                            RowID

                            ,ProductKey

                            ,ScriptMonth

                            ,Value

                            ,Measure

                          Resident T1;

                           

                          DROP Table DataviewValues1;

                          DROP table T1;

                          exit Script;