Data not showing up for some rows. Can we replicate data from previous rows?

[JonesBeach]

Hi Everyone,

I have a rather simple task but yet cant find a way around it. I have tried a couple tricks but could not get through. 

I have developed a dashboard where you can see the information pertaining to a name of a student. However for some reason some of the rows are appearing to be empty and I have to show them except for the Date Completed column only there I need to show "Not Available" but for columns College, Professor and Area of Study I want to show the same information as above row. Could someone please offer a helping hand in this!!

Thanks!!

Name	Place of birth	College	Professor	Topic	Date Completed	Area of Study
Rodney Pascal	Huntington, WV	Marshall University	J.Andrew. Shwartz	Child Psychology in poor neighborhoods 101	9/23/2014	Masters of Child Psychology
Rodney Pascal	Huntington, WV	Marshall University	J.Andrew. Shwartz	Child Psychology in poor neighborhoods 402	1/31/2010	Masters of Child Psychology
Rodney Pascal	Huntington, WV			Child Psychology in poor neighborhoods 111
Rodney Pascal	Huntington, WV			Child Psychology in poor neighborhoods 102
Rodney Pascal	Huntington, WV	Marshall University	J.Andrew. Shwartz	Child Psychology in poor neighborhoods 113	3/30/2021	Masters of Child Psychology
Rodney Pascal	Huntington, WV			Child Psychology in poor neighborhoods 104
Rodney Pascal	Huntington, WV			Child Psychology in poor neighborhoods 110

[brunobertels · Accepted solution]

Hi 

try this 

I just added a rowID to unsure the row data sorting order 

temp:
Load
RowID,
[Name],
[Place of birth],
if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),peek(College),
[College]) as College,

if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),peek(Professor),
[Professor]) as Professor,


[Topic],
if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),'Not Available',
Date(Date#([Date Completed], 'M/DD/YYYY') ) ) AS [Date Completed],

if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),peek([Area of Study]),
[Area of Study]) as [Area of Study]

From ... 

Hope it helps 

PS below the complete script with inline load 

[Table]:
LOAD *,
rowno() as RowID;

LOAD * INLINE
[
Name;Place of birth;College;Professor;Topic;Date Completed;Area of Study
Rodney Pascal;Huntington, WV;Marshall University ;J.Andrew. Shwartz;Child Psychology in poor neighborhoods 101;9/23/2014;Masters of Child Psychology
Rodney Pascal;Huntington, WV;Marshall University ;J.Andrew. Shwartz;Child Psychology in poor neighborhoods 402;1/31/2010;Masters of Child Psychology
Rodney Pascal;Huntington, WV; ; ;Child Psychology in poor neighborhoods 111; ;
Rodney Pascal;Huntington, WV; ; ;Child Psychology in poor neighborhoods 102; ;
Rodney Pascal;Huntington, WV;Marshall University ;J.Andrew. Shwartz;Child Psychology in poor neighborhoods 113;3/30/2021;Masters of Child Psychology
Rodney Pascal;Huntington, WV; ; ;Child Psychology in poor neighborhoods 104; ;
Rodney Pascal;Huntington, WV; ; ;Child Psychology in poor neighborhoods 110; ;
](delimiter is ';');

NoConcatenate
temp:
Load
RowID,
[Name],
[Place of birth],
if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),peek(College),
[College]) as College,

if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),peek(Professor),
[Professor]) as Professor,


[Topic],
if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),'Not Available',
Date(Date#([Date Completed], 'M/DD/YYYY') ) ) AS [Date Completed],

if(isnull(Date(Date#([Date Completed], 'M/DD/YYYY') )),peek([Area of Study]),
[Area of Study]) as [Area of Study]

resident Table ;
drop table Table;

[JonesBeach]

Hi @brunobertels This does work when I am trying with the dataset attached above but with my actual data it doesnt work. I think the reason for that is, I have two of the fields coming from another table and when I do a join between two table to make it one the problem remains there. 

If you can guide with that a bit I think I will be able to work through this.

Regards!

[brunobertels]

Hi 

May be you can post a sample of your different table so i will be able to test 

an excel file with a sample of data with one table per sheet 

regards 

[JonesBeach]

I got it to work but I am adding a Custom Calendar Object which isnt working now because the date column isnt in the correct format. Could you help with that? @brunobertels 

[brunobertels]

Hi 

What is your calendar object ? Date Picker object in QS ? 

What is your date format actually ?

regards 

[JonesBeach]

@brunobertels Thanks for all the help. I found a work around before applying the fix you suggested above I just created a copy of Date Column and kept it unchanged in its original date format and then when I created a Temp table(As shown in your solution) I just called it in with Name and used that Date column in the Custom Date Picker Object and now it all seems to work just fine. 

Thanks again for looking into this!