Comma Delimited Field

[michaeldburt]

Hi All-

I've come across a file that contains a field that is comma delimited.  Here is an example row:

Field1: ItemNumber

Field2: BuildingNumber

Field3: 12,13,14,12,143,14,14,13,13,17,19,18

Field4: Year

So, the row contains data for an Item, a Building, and Field 3 is a Monthly amount, delimited by the comma.  What I'd like is for the data from Field3 to either:

1) be assigned a number, 1-12, based on the position in the set of values

or

2)create 12 rows, 1 row containing each value in the set, and given a new column, like Field5, that is numbered 1-12, based on the position of the number in the set of values.

Any suggestions?

[Peter_Cammaert · Accepted solution]

Check out the CROSSTABLE Prefix. You can rearrange the LOAD statement so that the first two fields and the last one are loaded first, and the 12 month values are loaded next and rotated.

See: Crosstable ‒ QlikView

Peter

[MarcoWedel]

Hi,

one solution might be:

table1:
LOAD RecNo() as %ID, * INLINE [
    Field1, Field2, Field3, Field4
    Item1, Building1, "12,13,14,12,143,14,14,13,13,17,19,18", 2015
    Item1, Building1, "13,14,15,13,144,15,15,14,14,18,20,19", 2016
    Item1, Building2, "12,13,14,12,143,14,14,13,13,17,19,18", 2015
    Item1, Building2, "13,14,15,13,144,15,15,14,14,18,20,19", 2016
    Item2, Building1, "14,15,16,14,145,16,16,15,15,19,21,20", 2015
    Item2, Building1, "15,16,17,15,146,17,17,16,16,20,22,21", 2016
    Item2, Building2, "14,15,16,14,145,16,16,15,15,19,21,20", 2015
    Item2, Building2, "15,16,17,15,146,17,17,16,16,20,22,21", 2016
];

table2:
LOAD %ID,
    Month(Date#(IterNo(),'M')) as Month,
    SubField(Field3,',',IterNo()) as Amount
Resident table1
While IterNo()<=SubStringCount(Field3,',')+1;

hope this helps

regards

Marco

[michaeldburt]

Thanks Peter.  Looks like crosstable is going to be the easiest way to transform the data.  Marking as correct.