Loop through a field that contains multiple items and create new rows

[#1]

We import from a legacy system that hs a couple fields that are goofy.   In my Product Detail rows i have something like this - The Tally column is the one that needs help.

The order/product detail may look like this

Douglas Fir   <-- LineNO

      1x8  <- LineItem

          10/12ft , 10/20ft      <--Tally

     1x10

          100/10ft

Pine

     1x4

          200/6ft    200/10ft

Key     OrderNo     LineNo     LineItem     Tally

1          1               501          100           ~001012   ~001020

1          1               501          200           ~010010

1          1               502          100           ~020006   ~020010

I need to parse the Tally field by looping through identifying a new record and creating a new line at each ~ so that it would look like this

Key     OrderNo     LineNo     LineItem     TallyID     Qty     Size

1          1               501          100           100          10       12

1          1               501          100           200          10       20

1          1               501          200           100          100     10

1          1               502          100           100          200      6

1          1               502          100            200         200      10

I know for QTY and Size  i can parse using LEFT, RIGHT, MID

What I need help with is a loop through that field and identifying at each ~ make new record

[ramoncova06]

This can be done I just don't get how are you getting the tallyid number

[ziadm]

Hi

Lets make these assumption and the below code will work fine

- assuming that the separator is semicolon ; in order

- Leading separator from the fiels Tally is removed so you original table will look like this

- Tally is having the

Data1:

LOAD * INLINE [

    Key, OrderNo, LineNo, LineItem,      Tally

    1,      1,           501,      100,           001012   ;001020

    1,      1,           501,      200,           010010

    1,      1,           502,      100,           020006   ;020010

];

Data2:

LOAD

Key,

OrderNo,

LineNo,

LineItem,

left(Tally,3) as TallyID,    //Assuming that 1st 3 digits is the Tally ID

mid(Tally,4,1) as Qty, ////Assuming that 4st  digits is the Qty

mid(Tally,6,1) as Size ////Assuming that 6th  digits is the Size

Resident Data1

  While IterNo() <=  substringcount(Tally,';')+1 ;

// YOu may have to change the string functions mid and left in order to extract the right Field TallyID,Qty and Size

if this is helpful and right please mark as solved

Regards

Ziad

[ziadm]

Sorry Correction i missed to pick the field using the subfield function

Copy the below code and paste it

Data1:

LOAD * INLINE [

    Key, OrderNo, LineNo, LineItem, Tally

    1, 1, 501, 100, 001012   ;001020

    2, 1, 501, 200, 010010

    3, 1, 502, 100, 020006   ;030010

];

Data2:

LOAD

Key,

OrderNo,

LineNo,

LineItem,

// Loop as many as no of tokens to parse

// This should pick the right token using the subfield function

left(subfield(Tally,';',iterno()),3) as TallyID,    //Assuming that 1st 3 digits is the Tally ID

mid(subfield(Tally,';',iterno()),4,1) as Qty, ////Assuming that 4st  digits is the Qty

mid(subfield(Tally,';',iterno()),6,1) as Size ////Assuming that 4st  digits is the Size

  Resident Data1

  While IterNo() <=  substringcount(Tally,';')+1 ;

Drop Table Data1;

// Please do not forget to flag this blog if its helful or resolved

Regards

Ziad

[#5]

Actually TallyID needs to be a new autogenerated number of some sort.

I will give this a try but also found a couple more scenarios that need additional parsing.   Once I create the tally, I need to do a character replacement as our legacy system bring in negative numbers as an alpha....   will get to that later if this parse works.

Here is an example of the data - sometimes it has a character sometimes not.  When it is lumber it has no character - when it is plywood it has the char.   The second line is actually 224 pieces of 48x96" plywood.   So I will need to first separate my product types then parse through them as the info is a little different.  Gotta love legacy systems.....

04091510874.00	04091510874.00502	502610	189 PCS/UNIT	113406   113407 75608    75610    37812
07240667904.00	07240667904.00501	501610	2 UNITS	¬00022400¬00004800¬00009600

[rwunderlich]

SubField(Tally,'~')

will automatically generate a row for each value. You'll get a blank value because of the leading "~". That can be removed in a preceding load. AutoNumber() can be used to generate your new TallyIds. So...

Data1:

LOAD

  *,

  AutoNumber(Tally, OrderNo&LineNo&LineItem) * 100 as TallyId

Where len(Tally)>0

;

LOAD Key, OrderNo, LineNo, LineItem,

  trim(SubField(Tally,'~')) as Tally

INLINE [

    Key, OrderNo, LineNo, LineItem,      Tally

    1,      1,          501,      100,          ~001012  ~001020

    1,      1,          501,      200,          ~010010

    1,      1,          502,      100,          ~020006  ~020010

];

-Rob

http://masterssummit.com

http://robwunderlich.com

[#7]

TallyTemp:

LOAD

    *,

    AutoNumber(Prod_TallyDetail,Prod_LineNo&Prod_LineItem)*100 as TallyID

    Where len(Prod_TallyDetail)>0

    ;

LOAD

    KEY,

    Prod_LineNo             as LineNo,   

    Prod_LineItem         as LineItem,

    trim(SubField(Prod_TallyDetail,'   ')) as Tally

Resident Products;

------------------------------------------------------------

I keep getting Prod_TallyDetail not Found errors

[ramoncova06]

change this to   

AutoNumber(Prod_TallyDetail,Prod_LineNo&Prod_LineItem)*100 as TallyID

    Where len(Prod_TallyDetail)>0

to

    AutoNumber(Tally,Prod_LineNo&Prod_LineItem)*100 as TallyID

    Where len(Tally)>0

[#9]

Prod_TallyDetail is the actual field - in my original example I shortened the names

I ran Ziad's script and it worked except when I tried to put in "Where" clause because our system puts different data into the Prod_TallyDetail depending on the type of product.

[ramoncova06]

That is ok, but if you look at Rob code  he is using a preceding load https://community.qlikview.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load?author=2012/05

and you are changing the name to Tally that is why you are getting that error

LOAD

    KEY,

    Prod_LineNo            as LineNo,

    Prod_LineItem        as LineItem,

    trim(SubField(Prod_TallyDetail,'  ')) as Tally

Resident Products;