topic Re: Field missing in QlikView

Field missing

Wed, 25 Nov 2020 16:16:04 GMT

I am new to qlikview and would like to know how can I load flowfield from Nav

I tried to type the name of the field in load and / or select without success.

the flow field I need is in the job table "Total Budget"

Thank you for your help

JobTable:

LOAD

"No_" AS JobID,

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss";

SQL SELECT

"No_",

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss"

FROM test.dbo."db$Job";

Re: Field missing

sunny_talwar — Tue, 18 Oct 2016 22:46:06 GMT

flowfield? What is that? Can you elaborate a little?

Re: Field missing

Wed, 19 Oct 2016 07:02:24 GMT

Hi Sunny

It's a calculated field

Re: Field missing

nagarjuna_kotha — Wed, 19 Oct 2016 07:11:36 GMT

Can u please explain clearly?

So that we ll try to help you.

-Nagarjun

Re: Field missing

Wed, 19 Oct 2016 07:19:27 GMT

HI all

It appears my problem is not clear enough for everyone.

I have on table "Job" the following information, I would like to load field "Total Budget" in Qlikview

Enabled	Field No.	Field Name	Data Type	Length	Description	Field Class
Yes	1	No.	Code	20		Normal
Yes	3	Description	Text	50		Normal
Yes	4	Description 2	Text	50		Normal
Yes	5	Bill-to Customer No.	Code	20		Normal
Yes	12	Creation Date	Date			Normal
Yes	19	Status	Option			Normal
Yes	20	Person Responsible	Code	20		Normal
Yes	29	Last Date Modified	Date			Normal
Yes	58	Name	Text	50		Normal
Yes	67	Country/Region Code	Code	10		Normal
Yes	11012001	Total Budget	Decimal			FlowField

Re: Field missing

vinieme12 — Wed, 19 Oct 2016 07:27:37 GMT

Ask the database guys for the exact field name in the Job table and then add as below

JobTable:

LOAD

"No_" AS JobID,

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss"

"TOTAL BUDGET"

;

SQL SELECT

"No_",

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss"

"TOTAL BUDGET"

FROM test.dbo."db$Job";

Re: Field missing

Wed, 19 Oct 2016 07:32:56 GMT

I tried it but it's not working

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Invalid column name 'TOTAL BUDGET'.

Re: Field missing

vinieme12 — Wed, 19 Oct 2016 17:58:20 GMT

You need to know the exact name of the field in the table/view...ask the Database Admin

Re: Field missing

stabben23 — Wed, 19 Oct 2016 18:20:12 GMT

if you have a connection to the database, press select in script. Find your table test.dbo."db$Job"

Here can you see what fields the table have. Pick the one you need.

You can also do as select * and LOAD * to get all fields from the table.

Re: Field missing

maxgro — Wed, 19 Oct 2016 18:29:36 GMT

try with a select * and then in table viewer (CTRL-T) look at the fields you can load

SQL SELECT *

FROM test.dbo."db$Job";

Re: Field missing

vinieme12 — Wed, 19 Oct 2016 18:56:43 GMT

try like below

JobTable:

LOAD

"No_" AS JobID,

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss"

"Total Budget"

;

SQL SELECT

"No_",

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss"

"Total Budget"

FROM test.dbo."db$Job";

Re: Field missing

Wed, 19 Oct 2016 20:35:13 GMT

Hi all

Thank you for taking time to help me.

Below the table structure and the field I am trying to get. with select * from Job I manage to get all normal fields but not flowfield.

Is there a way to get Total Budget field?

Thanks

Re: Field missing

stabben23 — Thu, 20 Oct 2016 05:15:01 GMT

If this is the same table as above test.dbo."db$Job", then add "Total Budget" in Your Select.

SQL SELECT

"No_",

"Description",

"Bill-to Customer No_",

"Creation Date",

"Allow Precl_ R__Prov_ Loss",

"Total Budget"

FROM test.dbo."db$Job";

Re: Field missing

miskin_m — Thu, 20 Oct 2016 05:24:40 GMT

Hi,

Flow field can only be loaded by left joining with the current table. Look for the original table from where flow fields is coming. Flow field is nothing but it is calculated field for which data is coming from other table. Their is a setting where you will get to know from which table the flow field is coming. Ask database guy for the same.

Re: Field missing

tunoi — Thu, 20 Oct 2016 09:31:12 GMT

Flow fields in Dynamics are not available in the physical table, they are calculated so the only way to get them is to replicate the logic behind them in your script.

Re: Field missing

Miguel_Angel_Baeyens — Thu, 20 Oct 2016 10:44:33 GMT

Hello Adel,

As mentioned above, flow fields are not actual fields in the table, but calculations performed when the flow field is requested, and these are usually not stored back.

For some versions, there is a C/SIDE ODBC native driver which can read from them, but it was discontinued for newer versions.

If you don't have such driver, or not enough licenses to use them, then the only alternative is to indeed replicate the logic in Qlik either in the load script or while doing the SQL query to retrieve the SIFT table with the calculation.

About FlowFields (Microsoft): FlowFields Overview

About SIFT tables (Microsoft): SumIndexField Technology (SIFT)

Miguel

Re: Field missing

Thu, 20 Oct 2016 11:50:47 GMT

Hi Miguel

Thank you for your help

I will try to replicate the logic in Qlik I do not know how to use C/SIDE driver