topic Re: Comma Delimited Field in QlikView

Comma Delimited Field

michaeldburt — Tue, 27 Sep 2016 15:08:44 GMT

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

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?

Re: Comma Delimited Field

Peter_Cammaert — Tue, 27 Sep 2016 16:36:57 GMT

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

Re: Comma Delimited Field

MarcoWedel — Tue, 27 Sep 2016 22:33:33 GMT

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

Re: Comma Delimited Field

michaeldburt — Wed, 28 Sep 2016 01:35:07 GMT

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