Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
[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;
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
[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;