Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Not sure if you are expecting something like below?:
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;
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;