Re: Loop through fields to change fieldname/data t... - Qlik Community

Anonymous · ‎2015-04-21

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.

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)],

ali_hijazi · ‎2015-04-21

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

I can walk on water when it freezes

jonathandienst · ‎2015-04-21

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)];

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

jonathandienst · ‎2015-04-21

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Anonymous · ‎2015-04-23

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)

;

jonathandienst · ‎2015-04-23

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Anonymous · ‎2015-06-17

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

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

//SCRIPT USED:

DataviewValues1:

CrossTable(ScriptMonth_Measure, Value, 2)

LOAD *

FROM


	(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;

Loop through fields to change fieldname/data type