topic Re: Straight table Column with no data ? in QlikView

Straight table Column with no data ?

bhaveshp90 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the Straight table as shown below;

I am using Cost Code, Cost Area as Dimensions and Value, Value (USD) as expressions.

Expressions:

Value = Value

Value (USD) = Value_USD (Value/Currency_Rate as Value_USD in my script)

Why I am not able to see any data for Value (USD), Can anyone tell me what am I doing wrong here?

thanks

Bhavesh

Re: Straight table Column with no data ?

vishsaggi — Wed, 17 Oct 2018 21:11:31 GMT

Can you do like below and if possible can you share a sample to look into?

= Sum(Value)

= Sum(Value_USD)

Re: Straight table Column with no data ?

Anil_Babu_Samineni — Thu, 18 Oct 2018 04:46:39 GMT

Else, Use this feature as this enable for Straight table

Re: Straight table Column with no data ?

jonathandienst — Thu, 18 Oct 2018 05:14:37 GMT

If you are doing this calculation in script, I suggest that you upload the relevent script portion.. Otherwise its just guesswork.

Re: Straight table Column with no data ?

bhaveshp90 — Thu, 18 Oct 2018 12:40:45 GMT

I did change both the expressions to Sum(Value), Sum(Value_USD) but my values are doubled when I do that

Re: Straight table Column with no data ?

bhaveshp90 — Thu, 18 Oct 2018 12:41:30 GMT

As shown in the image below, my values are doubled when I do Sum of rows feature.

Re: Straight table Column with no data ?

bhaveshp90 — Thu, 18 Oct 2018 12:44:56 GMT

Jonathan, I am creating my table with Cost Code, Cost Area as dimensions and Value, Value_USD as expressions. This is the Calculation I am doing in Script;

Project_Cost:

Load Project_Number &'-'& Region &'-'& if (match(Country_Name,'US','UK','Canada')>0, Country_Name, Capitalize(Country_Name)) &'-'& Snapshot_Date as Project_Cost_Key,

if (match(Capitalize(Country_Name),'Germany','Belgium','France','Italy','Spain','Netherland')>0,'Europe',if (match(Country_Name,'US','UK','Canada')>0, Country_Name, Capitalize(Country_Name))) &'-'& Year(Snapshot_Date) as Project_Cost_Currency_Key,

Cost_Code,

Cost_Area,

Value,

Updated_OEC_Budget

FROM

(qvd);

left join (Project_Cost)

LOAD

Currency_Code as Project_Cost_Currency_Code,

Currency_Rate as Project_Cost_Currency_Rate

FROM

(qvd)

where Month(Snapshot_date)='01';

Project_Cost1:

NoConcatenate Load

Value/Project_Cost_Currency_Rate as Value_USD,

Updated_OEC_Budget/Project_Cost_Currency_Rate as Updated_OEC_Budget_USD,

Resident Project_Cost;

drop table Project_Cost;

Re: Straight table Column with no data ?

jonathandienst — Fri, 19 Oct 2018 14:09:30 GMT

Check that Project_Cost_Currency_Key is unique in the Currency_Rate table. And/or check the NoOfRow('Project_Cost') value with trace statements before and after the join. I am pretty sure that the join is doubling the rows.

Re: Straight table Column with no data ?

bhaveshp90 — Fri, 19 Oct 2018 19:47:56 GMT

Jonathan, you are right it is not a one-to-one relationship. I have missed another condition in the joining.

where Month(Snapshot_date)='01'

and Currency_Ref = 'USD';

Thank you for your explanation