Generic Load with new headers

[Joel_Holmberg]

Hello,

First of all I apologize if this has been anwered before, but I've spent the morning looking without any success..

I think what I'm trying to do is a generic load but I can't get it to work with the headers I need.

My data is the following;

Item	Unit	No In Unit	Balance	Price	X	X	X
1	A	1	100	10	X	X	X
1	B	10	100	10	X	X	X
1	C	100	100	8	X	X	X
2	A	1	200	20	X	X	X
2	D	50	200	20	X	X	X
3	E	1	50	30	X	X	X
4	B	1	10	40	X	X	X

So basically every "Item" can have up to three units. The units can be different. "No in unit" describes how many of the unit is in the first entry of the item (Example; on Item 1, there's 100 "Unit" A in a "Unit" C).

What I'd like to do is a table (doesn't matter if I have to change in script or via Set Analysis) that is displayed like below.

Item	Primary Unit	Primary Price	Secondary Unit	No in Secondary	Secondary Price	Tertiary Unit	No in Tertiary	Tertiary Price	Balance
1	A	10	B	10	10	C	100	8	100
2	A	20	D	50	20	-	-	-	200
3	E	30	-	-	-	-	-	-	50
4	A	40	-	-	-	-	-	-	10

So I need to create new headers and place them in secondary & tertiary based on their size in "No in unit". The primary unit always has 1 "No in unit". Currently I have a straight up simple load in one table.

Thank you so much for taking your time to read my question.

[Vegar]

Hi @Joel_Holmberg 

The example below might help you in finding a solution to your problem.

As you see in the picture the output looks like your desired output.

TmpOrderLabel:
LOAD dual(A,B) as Order
Inline [
A, B
Primary, 1
Secondary, 2
Tertiary, 3
];
Data:
LOAD
if(peek('Item')=Item, Peek('Order')+1, 1) as Order,
 * inline [ 
Item	Unit	No In Unit	Balance	Price	X	XX	XXX
1	A	1	100	10	X	X	X
1	B	10	100	10	X	X	X
1	C	100	100	8	X	X	X
2	A	1	200	20	X	X	X
2	D	50	200	20	X	X	X
3	E	1	50	30	X	X	X
4	B	1	10	40	X	X	X
] (delimiter is '	');

DROP TABLE TmpOrderLabel;
Base:
LOAD 
	Item, 
	only(Balance) as Balance,
	Only(X) as X,
	Only(XX) as XX,
	Only(XXX) as XXX
Resident Data
group by Item
;

	GL:
	Generic LOAD 
		Item, Order, Unit& '|'& Price & '|' & [No In Unit] as Value
	Resident Data;

Drop Table Data;



	CombinedTable:
   	Load distinct Item Resident Base;

 
   For each vTableName in 'GL.Primary','GL.Secondary','GL.Tertiary'

      Left Join (CombinedTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName
 
 LEFT JOIN (Base) LOAD
	Item,
  	SubField(Primary, '|',1 ) as [Primary Unit],
  	SubField(Primary, '|',2 ) as [Primary Price],  	
  	SubField(Primary, '|',3 ) as [No In Primary],
  	SubField(Secondary, '|',1 ) as [Secondary Unit],
  	SubField(Secondary, '|',2 ) as [Secondary Price],
  	SubField(Secondary, '|',3 ) as [No In Secondary],
  	SubField(Tertiary, '|',1 ) as [Tertiary Unit],
  	SubField(Tertiary, '|',2 ) as [Tertiary Price],
  	SubField(Tertiary, '|',3 ) as [No In Tertiary]
  Resident
  	CombinedTable
   ;
   Drop table CombinedTable