Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi Dears,
I have connected to DB to select Some Data from dsh_project_tasks and then do another load Operation to the data to transform into another form.
The problem is that the first Query resulted in a table Called [dsh_project_tasks ] and some Columns named [Temp_...]
And the second table named [Tasks_Table] has columns named [ Alloc_...]
When I use columns of [dsh_project_tasks ] table, they are available to create the graph
But when I use the columns of [Tasks_Table] , I get the error Bad field name: Alloc_Resource_TaskName
Knowing that I dont get any errors during Load data
====================================================================================
My Script
--------------
LIB Connect To PostgreSQL_localhost;
[dsh_project_tasks]:
Select @@@@@@@@t.rec_id as temp_task_id,
t.name as temp_task_name,
t.planned_starttime as temp_plannedStart,
t.planned_endtime as temp_plannedEnd,
t.actual_starttime as temp_actualStart,
t.actual_endtime as temp_actualEnd,
t.resource_id as temp_resource_id,
t.project_id as temp_projectId
from dsh_project_tasks t ;
Tasks_Table:
LOAD temp_task_name as Alloc_Resource_TaskName ,
temp_resource_id as Alloc_Resource_Id,
If(IterNo() = 1,
RangeMin(temp_actualEnd, Floor(MonthEnd(temp_actualStart)) ) - temp_actualStart,
RangeMin(temp_actualEnd, Floor(MonthEnd(Start, IterNo() - 1))) - MonthStart(temp_actualStart, IterNo() - 1)) + 1 as Alloc_Resource_Days,
Month(MonthStart(temp_actualStart, IterNo() - 1)) as Alloc_Resource_Month,
Year(MonthStart(temp_actualStart, IterNo() - 1)) as Alloc_Resource_Year
While IterNo() <= MonthDiff;
LOAD *,
((Year(temp_actualEnd)*12) + Month(temp_actualEnd)) - (((Year(temp_actualStart)*12) + Month(temp_actualStart))) + 1 as MonthDiff;
============================================================================
What's wrong with the Scripts ordering ???
Seems like you are looking to do a preceding load here. The order would be like this:
Tasks_Table:
LOAD temp_task_name as Alloc_Resource_TaskName ,
temp_resource_id as Alloc_Resource_Id,
If(IterNo() = 1,
RangeMin(temp_actualEnd, Floor(MonthEnd(temp_actualStart)) ) - temp_actualStart,
RangeMin(temp_actualEnd, Floor(MonthEnd(Start, IterNo() - 1))) - MonthStart(temp_actualStart, IterNo() - 1)) + 1 as Alloc_Resource_Days,
Month(MonthStart(temp_actualStart, IterNo() - 1)) as Alloc_Resource_Month,
Year(MonthStart(temp_actualStart, IterNo() - 1)) as Alloc_Resource_Year
While IterNo() <= MonthDiff;
LOAD *,
((Year(temp_actualEnd)*12) + Month(temp_actualEnd)) - (((Year(temp_actualStart)*12) + Month(temp_actualStart))) + 1 as MonthDiff;
Select @@@@@@@@t.rec_id as temp_task_id,
t.name as temp_task_name,
t.planned_starttime as temp_plannedStart,
t.planned_endtime as temp_plannedEnd,
t.actual_starttime as temp_actualStart,
t.actual_endtime as temp_actualEnd,
t.resource_id as temp_resource_id,
t.project_id as temp_projectId
from dsh_project_tasks t ;
Seems like you are looking to do a preceding load here. The order would be like this:
Tasks_Table:
LOAD temp_task_name as Alloc_Resource_TaskName ,
temp_resource_id as Alloc_Resource_Id,
If(IterNo() = 1,
RangeMin(temp_actualEnd, Floor(MonthEnd(temp_actualStart)) ) - temp_actualStart,
RangeMin(temp_actualEnd, Floor(MonthEnd(Start, IterNo() - 1))) - MonthStart(temp_actualStart, IterNo() - 1)) + 1 as Alloc_Resource_Days,
Month(MonthStart(temp_actualStart, IterNo() - 1)) as Alloc_Resource_Month,
Year(MonthStart(temp_actualStart, IterNo() - 1)) as Alloc_Resource_Year
While IterNo() <= MonthDiff;
LOAD *,
((Year(temp_actualEnd)*12) + Month(temp_actualEnd)) - (((Year(temp_actualStart)*12) + Month(temp_actualStart))) + 1 as MonthDiff;
Select @@@@@@@@t.rec_id as temp_task_id,
t.name as temp_task_name,
t.planned_starttime as temp_plannedStart,
t.planned_endtime as temp_plannedEnd,
t.actual_starttime as temp_actualStart,
t.actual_endtime as temp_actualEnd,
t.resource_id as temp_resource_id,
t.project_id as temp_projectId
from dsh_project_tasks t ;
It seems your not showing the complete script.
There are not fields named [Task_...] in your first table load, your second table load misses a table source to load from.
This mean that I can rename the columns in the first load using (Select) statement,
and then I can rename them again in the (Load) statement,
I think this is the obstacle
The order you suggested worked in case I say
Select *
from dsh_project_tasks t ;
without using aliases
So is it working or is it not working? I am sorry, I am not sure I understand the question or is there a question or is this resolved?