Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
shaimamahmoud
Not applicable

Some Columns are not seen to Qlik Sense

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

stalwar1

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Some Columns are not seen to Qlik Sense

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 ;

5 Replies
sunny_talwar
Not applicable

Re: Some Columns are not seen to Qlik Sense

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 ;

swuehl
Not applicable

Re: Some Columns are not seen to Qlik Sense

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.

shaimamahmoud
Not applicable

Re: Some Columns are not seen to Qlik Sense

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

shaimamahmoud
Not applicable

Re: Some Columns are not seen to Qlik Sense

Thanks for the notification, I edited the Question

And yes, this is the complete script

sunny_talwar
Not applicable

Re: Some Columns are not seen to Qlik Sense

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?