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
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;
