Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table that contains a KEY (in my case [Part]), the [Part] is listed as many times as there are entries.
Example:
XRefCount | Part | VendorID | VendorName | VendorPart |
1 | 111-1122-22 | 26 | Vendor 1 | Part-1 |
1 | 222-2211-11 | 103 | Vendor 2 | Part-2 |
2 | 222-2211-11 | 150 | Vendor 3 | Part-3 |
3 | 222-2211-11 | 250 | Vendor 4 | Part-4 |
1 | 122-3333-33 | 300 | Vendor 5 | Part-5 |
2 | 122-3333-33 | 350 | Vendor 6 | Part-6 |
3 | 122-3333-33 | 500 | Vendor 7 | Part-7 |
I want to have 1 table with Distinct [Part] and have a field for each value, VendorID1, VendorID2, VendorID3...ect.
End Result:
Part | VendorID1 | VendorName1 | VendorPart1 | VendorID2 | VendorName2 | VendorPart2 | VendorID3 | VendorName3 | VendorPart3 |
111-1122-22 | 26 | Vendor 1 | Part-1 | ||||||
222-2211-11 | 103 | Vendor 2 | Part-2 | 150 | Vendor 3 | Part-3 | 250 | Vendor 4 | Part-4 |
122-3333-33 | 300 | Vendor 5 | Part-5 | 350 | Vendor 6 | Part-6 | 500 | Vendor 7 | Part-7 |
My data table has up to 8 [VendorID] values per [Part]. as well as 7 Fields (besides the [Part] and [XrefCount]) that I need to have numbered fields up to 8.
There are 43k rows in this table, and 40k distinct [Part]'s.
Thank you in Advance!
the error is below, the variable is not being cleared before generation of the fieldlist
Add the below line just before For loop
let vColList='';
for i = 1 to $(vMax);
vColList = '$(vColList)' & if($(i)=1,'',',') &'MaxString(if(XRefCount=$(i),VendorID)) as VendorID$(i)
, MaxString(if(XRefCount=$(i),VendorName)) as VendorName$(i)
, MaxString(if(XRefCount=$(i),VendorPart)) as VendorPart$(i)' ;
next i;
@vinieme12 Awesome thank you!!
I didn't know you need to clear the variable before the loop in the load script. But that makes sense now.