Create Transposed table during load process.

[johngouws]

Hello All. 

I have a table containing Item Codes, which have a few preferred suppliers defined. Some items may have 0 defined and other may have 3 preferred suppliers. In the tables they are listed below each other. I am looking for a way of transposing these, at load time, so that the preferred suppliers are in a single row, next to the item code. I have been trying with Generic and Crosstable but cannot get the correct outcome. I appreciate any guidance with this. 

Thank you in advance.

[DavidM]

I don't think you can load this with crosstable. I would do 3 loads:

First load load primary supplier (should work with load distinct).

Second load filter only those which have two suppliers with count, load only even lines

Third load filter those with three suppliers and load every third line.

Join/concatenate

[johngouws]

Thanks David, I will give that a crack. 

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    ItemCode, SupplierCode, SupplierRef
    B1300HP, INH001, 2/B1300Z
    B1300HP, MOD001, 2/B1300Z
    C0910HP, MER001, 1523520EM
    C0910HP, SOU001, ROPAL
    D2350RM, CRE001, D234Z
    D2350RM, INH001, 2/D2350Z
    H5120MP, CRE001, 2/H5120Z
    H5120MP, INH001, 2/H5120Z
    H5120RP, CRE001, 2/H5120Z
    H5120RP, INH001, 2/H5120Z
    H7370MP, INH001, 2/H7380Z
    H7370MP, PRO001, 2/
    H7370MP, MER001, 2/H737Z
    INOC-P-10, JPA001
    INOC-P-10, CCI001
    M5100HP, INH001, 2/M5100Z
    OXO-CM0309B, THE002
    T7720M, SIG001, 258776
    T7720M, ING001, 2/T7720Z
    T7720M, MON002, VWR 83630.320
];

tmpTable:
LOAD *,
	 If(ItemCode = Previous(ItemCode), RangeSum(1, Peek('Num')), 1) as Num
Resident Table
Order By ItemCode;

DROP Table Table;

FinalTable:
LOAD DISTINCT ItemCode
Resident tmpTable;

FOR i = 1 to FieldValueCount('Num')

	LET vFieldValue = FieldValue('Num', $(i));
	LET vLetter = Chr($(i) + 64);
	
	Left Join (FinalTable)
	LOAD DISTINCT ItemCode,
		 SupplierCode as [SupplierCode$(vLetter)],
		 SupplierRef as [SupplierRef$(vLetter)]
	Resident tmpTable
	Where Num = $(vFieldValue);
	
NEXT

DROP Table tmpTable;

[johngouws]

Hi Sunny. Thank you very much. That is absolutely perfect. 

For info - I just ran that against 140000 stock items, and it took about 4 seconds. 

Thank you.

[sunny_talwar]

That seems acceptable. I am glad it worked 🙂