Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
bhaveshp90
Contributor III

Straight table Column with no data ?

Hello,

I have the Straight table as shown below;

Screenshot (5).png

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Straight table Column with no data ?

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
vishsaggi
Esteemed Contributor III

Re: Straight table Column with no data ?

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 ?

Else, Use this feature as this enable for Straight table

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

Re: Straight table Column with no data ?

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
bhaveshp90
Contributor III

Re: Straight table Column with no data ?

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

bhaveshp90
Contributor III

Re: Straight table Column with no data ?

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

bhaveshp90
Contributor III

Re: Straight table Column with no data ?

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

     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,

     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;

MVP
MVP

Re: Straight table Column with no data ?

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

bhaveshp90
Contributor III

Re: Straight table Column with no data ?

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