Help: Create 'FieldName'&1, 'FieldName'&2... ect from one 'FieldName' in one table.

[cbailey1201]

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!

[Gabbar]

Animal:
load * Inline [
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
];
Test:
Generic Load
Part,
XRefCount,
VendorID
Resident Animal;
TeamMembersJoin:
Load Distinct Part Resident Animal;
FOR i = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(i));
IF WildMatch('$(vTable)','GenericTeamMembers.*') THEN
LEFT JOIN ([TeamMembersJoin]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
Drop Table Animal;


Now repeat the same for VendorName and VendorPart 
and then join all 3 tables with Part column as joining field.
Also rename the columns as the column names will be derived from XrefCount each time.

[vinieme12 · Accepted solution]

As below

temp:
load * inline [
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
];

maxCount:
load max(XRefCount) as maxXRefCount
Resident temp;

let vMax = peek('maxXRefCount',0,'maxCount');
Drop table maxCount;

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;

Main:
Load
Part
,$(vColList)
Resident temp
Group by Part;
drop table temp;

exit Script;

[cbailey1201]

Gabbar,

This left me with a table for each [VendorID], and the join table doesn't have the fields.

[Gabbar]

I do not know why that is happening, I am trying to find out meanwhile try this:

Animal:
load * Inline [
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
];
GenericLabel:
Generic Load
Part,
XRefCount,
VendorID
Resident Animal;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable:
Load distinct Part Resident Animal;
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

A:
load Part, "1" as VendorID1,"2" as VendorID2 ,"3" as VendorID3 Resident CombinedGenericTable;

drop Table CombinedGenericTable;

GenericLabel1:
Generic Load
Part,
XRefCount,
VendorName
Resident Animal;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel1' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable1:
Load distinct Part Resident Animal;
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable1) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

B:
load Part, "1" as VendorName1,"2" as VendorName2 ,"3" as VendorName3 Resident CombinedGenericTable1;
drop Table CombinedGenericTable1;

GenericLabel2:
Generic Load
Part,
XRefCount,
VendorPart
Resident Animal;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel2' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
CombinedGenericTable2:
Load distinct Part Resident Animal;
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable2) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

C:
load Part, "1" as VendorPart1,"2" as VendorPart2 ,"3" as VendorPart3 Resident CombinedGenericTable2;
drop Table CombinedGenericTable2;
Drop Table Animal;

NoConcatenate
Animal:
Load * Resident A;
Left Join
Load * Resident B;
Left Join
Load * Resident C;

Drop Table A,B,C;

[cbailey1201]

Thank you Gabbar,

I did something similar without the Generic Load statement.

XRefTmp:
load * Inline [
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
];

XRef:
NoConcatenate
Load Distinct
Part
Resident XRefTmp;

Left Join (XRef)
Load
Part,
If(XRefCount = '1', VendorID) as VendorID1,
If(XRefCount = '1', VendorName) as VendorName1,
If(XRefCount = '1', VendorPart) as VendorPart1,
If(XRefCount = '1', MFGName) as MFGName1,
If(XRefCount = '1', MFGPart) as MFGPart1,
If(XRefCount = '1', XRefStatus) as XRefStatus1,
If(XRefCount = '1', XRefComment) as XRefComment1
Resident XRefTmp
Where XRefCount = '1';

Left Join (XRef)
Load
Part,
If(XRefCount = '2', VendorID) as VendorID2,
If(XRefCount = '2', VendorName) as VendorName2,
If(XRefCount = '2', VendorPart) as VendorPart2,
If(XRefCount = '2', MFGName) as MFGName2,
If(XRefCount = '2', MFGPart) as MFGPart2,
If(XRefCount = '2', XRefStatus) as XRefStatus2,
If(XRefCount = '2', XRefComment) as XRefComment2
Resident XRefTmp
Where XRefCount = '2';

///////continue numbering fields 1-8///////

Drop table XRefTmp;

[vinieme12]

@cbailey1201 have you tried the script I posted?

[Gabbar]

@cbailey1201   using if will only work when you have very limited amount of data, when the distinct values of Xrefcount goes above 100 one will find trouble so using If is not a solution if your data updates regularly.
You should try the solution given by @vinieme12 , it is compact as well as works perfectly.

[cbailey1201]

Hello Vinieme12,

I have just tried your load, but i come across an error.

The following error occurred:

Unexpected token: '(', expected one of: ',', 'AutoGenerate', 'From', 'From_Field', 'Inline', 'Resident', 'Extension', ...

The error occurred here:

Main:

Load

Part

,MaxString(if(XRefCount=1,VendorID)) as VendorID1

, MaxString(if(XRefCount=1,VendorName)) as VendorName1

, MaxString(if(XRefCount=1,VendorPart)) as VendorPart1,MaxString(if(XRefCount=2,VendorID)) as VendorID2

, MaxString(if(XRefCount=2,VendorName)) as VendorName2

, MaxString(if(XRefCount=2,VendorPart)) as VendorPart2,MaxString(if(XRefCount=3,VendorID)) as VendorID3

, MaxString(if(XRefCount=3,VendorName)) as VendorName3

, MaxString(if(XRefCount=3,VendorPart)) as VendorPart3,MaxString(if(XRefCount=4,VendorID)) as VendorID4

, MaxString(if(XRefCount=4,VendorName)) as VendorName4

, MaxString(if(XRefCount=4,VendorPart)) as VendorPart4,MaxString(if(XRefCount=5,VendorID)) as VendorID5

, MaxString(if(XRefCount=5,VendorName)) as VendorName5

, MaxString(if(XRefCount=5,VendorPart)) as VendorPart5,MaxString(if(XRefCount=6,VendorID)) as VendorID6

, MaxString(if(XRefCount=6,VendorName)) as VendorName6

, MaxString(if(XRefCount=6,VendorPart)) as VendorPart6,MaxString(if(XRefCount=7,VendorID)) as VendorID7

, MaxString(if(XRefCount=7,VendorName)) as VendorName7

, MaxString(if(XRefCount=7,VendorPart)) as VendorPart7,MaxString(if(XRefCount=8,VendorID)) as VendorID8

, MaxString(if(XRefCount=8,VendorName)) as VendorName8

, MaxString(if(XRefCount=8,VendorPart)) as VendorPart8MaxString>>>>>>(<<<<<<if(XRefCount=1,VendorID)) as VendorID1

, MaxString(if(XRefCount=1,VendorName)) as VendorName1

, MaxString(if(XRefCount=1,VendorPart)) as VendorPart1,MaxString(if(XRefCount=2,VendorID)) as VendorID2

, MaxString(if(XRefCount=2,VendorName)) as VendorName2

, MaxString(if(XRefCount=2,VendorPart)) as VendorPart2,MaxString(if(XRefCount=3,VendorID)) as VendorID3

, MaxString(if(XRefCount=3,VendorName)) as VendorName3

, MaxString(if(XRefCount=3,VendorPart)) as VendorPart3,MaxString(if(XRefCount=4,VendorID)) as VendorID4

, MaxString(if(XRefCount=4,VendorName)) as VendorName4

, MaxString(if(XRefCount=4,VendorPart)) as VendorPart4,MaxString(if(XRefCount=5,VendorID)) as VendorID5

, MaxString(if(XRefCount=5,VendorName)) as VendorName5

, MaxString(if(XRefCount=5,VendorPart)) as VendorPart5,MaxString(if(XRefCount=6,VendorID)) as VendorID6

, MaxString(if(XRefCount=6,VendorName)) as VendorName6

, MaxString(if(XRefCount=6,VendorPart)) as VendorPart6,MaxString(if(XRefCount=7,VendorID)) as VendorID7

, MaxString(if(XRefCount=7,VendorName)) as VendorName7

, MaxString(if(XRefCount=7,VendorPart)) as VendorPart7,MaxString(if(XRefCount=8,VendorID)) as VendorID8

, MaxString(if(XRefCount=8,VendorName)) as VendorName8

, MaxString(if(XRefCount=8,VendorPart)) as VendorPart8

Resident XRefTmp

Group by Part

[cbailey1201]

Vinieme12,

I created a new app to try and troubleshoot the load script you provided. And It loads perfectly fine the first time.

However if I load it a second time it errors out

The following error occurred:

Unexpected token: '(', expected one of: ',', 'AutoGenerate', 'From', 'From_Field', 'Inline', 'Resident', 'Extension', ...