Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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!
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
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
Hi
What is your calendar object ? Date Picker object in QS ?
What is your date format actually ?
regards
@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!