topic Re: Loop through a field that contains multiple items and create new rows in QlikView

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

Mon, 13 Apr 2015 23:37:05 GMT

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

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

ramoncova06 — Tue, 14 Apr 2015 02:32:07 GMT

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

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

ziadm — Tue, 14 Apr 2015 06:36:50 GMT

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

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

ziadm — Tue, 14 Apr 2015 07:22:01 GMT

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

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

Tue, 14 Apr 2015 16:30:15 GMT

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

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

rwunderlich — Tue, 14 Apr 2015 17:08:09 GMT

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

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

Tue, 14 Apr 2015 18:02:31 GMT

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

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

ramoncova06 — Tue, 14 Apr 2015 18:10:05 GMT

change this to

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

Where len(Prod_TallyDetail)>0

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

Where len(Tally)>0

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

Tue, 14 Apr 2015 18:51:25 GMT

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.

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

ramoncova06 — Tue, 14 Apr 2015 18:55:56 GMT

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;