Concat with Resident table

[pnvenkat]

Hello,

I have a data set that has three columns: ID, object, and days_delta. ID corresponds to a customer, object corresponds to what object they bought, and days_delta corresponds to how many days after becoming a customer they bought the object. In the first code chunk, I created a table (Object_table) in which I can filter for null objects and days_delta. In the next code chunk, I want to concatenate the objects in the order of their days_delta, excluding all objects for which there is a `Null` days_delta. I am having issues in the second code chunk with concatenating the objects using resident object_table. Any guidance you might have to fix this issue is much appreciated!

[Object_table]:
NoConcatenate
Load id as id,
object_1,
if(object_2 > 0, object_2, 'Null') as object_2,
if(len(days_delta) > 0, days_delta, 'Null') as days_delta
Resident temp_table_1
Drop table temp_table_1;              //WORKS TILL HERE, THIS CHUNK PUTS 'NULL' WHERE NULL VALUES EXIST 

NoConcatenate
[temp_table_2]:
NoConcatenate
Load id as id,
days_delta as days_delta_no_nulls,
Concat(object_2, ', ') AS complete_object_2                 //WORKS IF I TAKE OUT CONCAT, GROUP BY, WHERE, AND ORDER BY
Resident object_table
GROUP BY id

WHERE days_delta <> 'Null'

ORDER BY days_delta;

[vikramv]

Hi ,

Your Group By , Where and Order By are Misplaced . Also "days_delta" is missing in Group By.

Try like this...

[temp_table_2]:
NoConcatenate
Load id as id,
days_delta as days_delta_no_nulls,
Concat(object_2, ', ') AS complete_object_2 
Resident object_table
WHERE days_delta <> 'Null'
GROUP BY id,days_delta
ORDER BY days_delta;

Thanks.

Vikky

[pnvenkat · Accepted solution]

[Object_table]:
NoConcatenate
Load id as id,
object_1,
if(object_2 > 0, object_2, 'Null') as object_2,
if(len(days_delta) > 0, days_delta, 'Null') as days_delta
Resident temp_table_1
Drop table temp_table_1;

[temp_table_2]:
Load id, days_delta, object_2
Resident Object_table
WHERE days_delta <> 'Null'
Order BY days_delta asc;

Left Join (Object_table)
Load Id,
Concat(object_2, ', ',days_delta) AS complete_object_2
Resident temp_table_2
GROUP BY Id;