Generic Load Maybe?

[haymarketpaul]

I have the folliowing data where each row has a type of 1

Pot1

ID	SomeText	Type
1	text1	1
2	text2	1
3	text3	1

I have another pot of data that looks like this (has many types other than 1)

Pot2

ID	SomeText2	Type
1	text	2
3	text	5
5	text	5
1	text	4

I need to consolidate the two so that i have one row for each ID and also maintain (in an outer joined table maybe?) which types they have

So from the data above i would need to know

ID	Type
1	1
1	2
1	4
2	1
3	1
3	5
5	5

so that a user would be able to filter the records based on one or more Types they select

Is there a good way to handle this in the load script somehow

[swuehl]

This should give you the last table:

LOAD ID, Type

FROM Pot1Table;

LOAD ID, Type

FROM Pot2Table;

I haven't fully understood how you want to consolidate the other fields into one record per ID.

[haymarketpaul]

Sorry wasn't clear i think...

I seem to be ending up with something like this

eg with ID # 1

ID	SomeText	SomeText2	Type
1	text2		1
1		text	2
1		text	4

i would like it to be like this

ID	SomeText	SomeText2
1	text2	text

And then either another column with the types listed like so 1,2,4 or better still perhaps if the are in a separate table so the user can select certain types and filter the data

[krishna_2644]

Not sure if you are expecting something like below?:

[settu_periasamy]

Hi,

May be like this?

A:
LOAD ID, 
     SomeText, 
     SomeText2, 
     Type
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);

New:
LOAD Distinct ID,SomeText
Resident A Where Not IsNull(SomeText);
Left Join(New)
LOAD Distinct ID,SomeText2
Resident A Where Not IsNull(SomeText2);

DROP Table A;

[swuehl]

Concerning the ID/SomeText/SomeText2 table, you can try joining Pot1 and Pot2 as shown above.

Alternatively you can do it like

TMP:

LOAD ID, SomeText

FROM Pot1Table;

CONCATENATE (TMP)

LOAD ID, SomeText2

FROM Pot2Table;

CONSOLIDATED:

LOAD ID,

          Only(SomeText) as SomeText,

          Only(SomeText2) as SomeText2

RESIDENT TMP

GROUP BY ID;

DROP TABLE TMP;

This would imply that you only have a unique SomeText resp. SomeText2 value per ID.

If this is not the case, but you still want a single records per ID, you can use concat:

CONSOLIDATED:

LOAD ID,

          Concat(SomeText,', ') as SomeText,

          Concat(SomeText2,', ') as SomeText2

RESIDENT TMP

GROUP BY ID;