How to integrate a do while loop within a for each loop

[DennisvD]

Hello,

I'm facing the following problem and hope I can explain it well.

There is the following table

Table:

Item | Status | Alternative item

A       |     x        |     B

B      |     x        |     D

C     |              |      I

D    |        x        |    E

E    |                  |   H

F    |                  |  K

For this  I need the following calculation.

If Item is not available (x), there needs to be a check on the alternative item.

I need to know from each item which is the available alternative and the road to it.
Table Solution would look as following

Aggr | Item | Alternative Item

A       |    A      |   B

A      |    B      |    D

A      |    D     |     E

B    |     B      |     D

B   |     D      |     E

D   |    D      |     E

So the check is only done for items that are not available untill first available substitute is found. The items won't be in order, because can replacements can be added any time and removed at any time too (so i dont think peek or previous can be used).

So  an applymap() will be used to find the replacement item. This data is available (in my eyes basically the table itself, because all this information is in the table).

My idea was to start with put filter on table to only check if status is 'x' and where there is an alternative item

Then comes to outerloop,  (for each loop).

For each vItem in FieldValueList('Item') (this is to check for all items which arent filtered out and to get the field: Aggr

But then comes my problem, how can I add her a do while loop. To keep searching till condition is met (applymap doesnt have a postitive result). My idea was to have vItem to aggr column and also items for the first loop (row) which gives me a replacement item with applymap on the input.

Then this needs to be saved in a variable to get the replacement item for that current item. (then row 1 is done)

And after that the replacement item needs to moved to a new row in the item column to use as input again for the applymap() (row 2) untill  there is no replacement is found.  After that u go to the next item in Item column and start it all over again till all Items are done

Is this possible and how would I need to write this? My main problems are:

- how to set the variables so they can be used in the loop and that after row 1 the outcome from the mapping will become item of next row and by that also the input of the next mapping iteration untill conditon is met and broken out of innerloop.

- How to nest the do while loop in the for each loop.

[MayilVahanan]

Hi @DennisvD 

May be , try like this

Temp:
Load * Inline
[
Item | Status | Alternative item

A | x | B

B | x | D

C | | I

D | x | E

E | | H

F | | K
] (delimiter is '|');

NoConcatenate
XItem:
Load * Resident Temp where Status = 'x';
Join
Load Distinct Item as Aggr Resident XItem;

Load * where Flag <> 0;
Load If(Peek(Aggr)<> Aggr, If(Aggr = Item, 1, 0), If(Aggr = Peek(Aggr) and Aggr = Item, 1, If(Peek('Flag') =0, 0, Peek('Flag')+1))) as Flag, *
Resident XItem order by Aggr;

DROP Field Flag;
DROP Table Temp, XItem;

O/p:

[DennisvD]

@MayilVahanan

Unfortunately this solution won't work. Because if not related product C in this list is also not available, it will get taken in the aggregation for item A.

In the current situation it would work, but it was just a quick overview of the situation. But in the actual data the items have all their own product number and not sequential and can't be ordered. Thats why I feel like there has to be an applymap() to find the correct substitute for each item.

Because the following situation is entirely possible.

A | x | X

B | x | C

C|     | Z

..... (rows with different items.

X|   | Y

Between A and the subsitute could be 10k+ items that are unavailable. Thats why the matching has to be done with an applymap, because there is no sorting order of any kind. Just for each Item in the database is added which is the subsitute for this particular Item.

In some situations 2 Items could have a several subsitutes and in the end connect both to the same substitute.

[DennisvD · Accepted solution]

Found the solution.

Temp:
Load * Inline
[
Item | Status | Alternative

A | x | B

B | x | D

C |x | I

D | x | E

E | | H

F | | K
] (delimiter is '|')
WHERE Status = 'x' AND len(Alternative)>0;

MAPPINGTABEL1:
Mapping
Load
Item,
[Alternative]
Resident Temp
WHERE Status = 'x' AND len(Alternative)>0;;

FOR Each a in FieldValueList('Item')
    LET vVar1 = a;
    LET vVar2 = a;
    Do until vVar2 = 'not mapped'
        If ApplyMap('MAPPINGTABEL1', '$(vVar2)', 'not mapped') <> 'not mapped' Then
            DataToSave:
            LOAD
            '$(vVar1)' as [aggr],
            '$(vVar2)' as [art],
           ApplyMap('MAPPINGTABEL1', '$(vVar2)', 0) as [Alt]
          AutoGenerate 1;
        END IF
        Let vVar2 = ApplyMap('MAPPINGTABEL1', '$(vVar2)', 'not mapped');
    LOOP
next a

[Saravanan_Desingh · Accepted solution]

One more solution using HierarchyBelongsTo:

tab1:
HierarchyBelongsTo ([Alt Item], [Item], Node, AncestorID, AncestorName, DepthDiff) 
LOAD *, [Item] As Node
Where Status='X';
LOAD * INLINE [
    Item, Status, Alt Item
    A, X, B
    B, X, D
    C, , I
    D, X, E
    E, , H
    F, , K
];

[Saravanan_Desingh]

Output:

[DennisvD]

Nice solution.
Do you know which performance will have a better solution when there will be a lot of rows?

Say like 10k+ rows?

[Saravanan_Desingh]

You are looping through each record which consumes more resource than this solution.

[DennisvD]

Thanks,

Only minor thing I found is that I cant use the name Item for Item. I could change Ancestor into aggr.

So would have to reload table and then rename Node back into Item again. Or maybe I did something wrong here.

Do you know if that is possible to use the original names?

[Saravanan_Desingh]

Try something like this,

tab1:
HierarchyBelongsTo ([Alt Item], [_Item], Item, AncestorID, Aggr, DepthDiff) 
LOAD *, [Item] As _Item
Where Status='X';
LOAD * INLINE [
    Item, Status, Alt Item
    A, X, B
    B, X, D
    C, , I
    D, X, E
    E, , H
    F, , K
];