topic Straight Table Expression Help in QlikView

Straight Table Expression Help

Ankhi — Sat, 16 Nov 2024 00:15:02 GMT

I have to create two straight table/table box with the below requirement.
My qvd has all the columns Project_name,Task,Amount and many more. Amount is a floating point number and i can have values like 0.00,but I want to ignore those as well in my expression. I don't need to do any sum of Amount. Just need to show the data as is but with the below condition.
Can you please help me with the set expression that I will need to write in the Straight Table expression to replace the below two sql queries.

1st Table:
Select Project_name,Task,Amount
where Project_name is null and Amount<>0
order by Project_name,Task

2nd Table:
Select Project_name,Task,Amount
where Task is null and Amount<>0
order by Project_name,Task

Thanks in advance.
Regards
Ankhi

Re: Straight Table Expression Help

Kushal_Chawda — Mon, 27 Jul 2020 21:25:53 GMT

Assuming that all 3 columns make a unique records then try below

For table 1, create straight table. You can change the sort order in Sort tab

Dimensions:

1) Project_name

2) =if(len(trim(Project_name))=0,Task,null()) ----> check 'suppress when value is null' option for this

Expression:

if(Amt>0,Amt,0) ---> Make sure that 'suppress zero values' option checked in presentation

For table 2, create straight table. You can change the sort order in Sort tab

Dimensions:

1) Project_Name

=if(len(trim(Task))=0,Project_name,null()) ------->check 'suppress when value is null' option for this

2) Task

Expression:

if(Amt>0,Amt,0) ---> Make sure that 'suppress zero values' option checked in presentation

Re: Straight Table Expression Help

Ankhi — Tue, 28 Jul 2020 12:14:17 GMT

Hi Kush,

Thanks for replying. Sorry I think I should have explained it a bit more. Below is the data that I have in QVD.

P6_Prj_No P6_Prj_Name Ora_proj_Num Task_num Level1 Level2 Level3 Cost_Remaining

P6_123 Allension_Main EB_321 033.01 C AR D 100

P6_234 XYZ 033.01 X AR Y 200

P6_543 ABC 033.02 X AR F

P6_789 CDC EB_123 B AR Z 300

P6_765 LXD E AR Q

P6_989 PTT L AR M 400

Now in my 1st Straight table , I want to only show data which has not got Ora_Proj_Num and Cost_Remaining >0

i.e.

Only P6_234(2nd row) should be returned. and Task_num is not required in this table. Similarly in the 2nd straight table

I only want to show data related to P6_989(last row) as this is the one which does not have a task_num but has a cost. This table does not need Ora_Proj_Num.

Also in both these strt tables I want to set a filter of Level2 in (AR,SR). Not sure exactly where I should that do.

Hope I have been able to explain my requirement.

Regards

Ankhi

Re: Straight Table Expression Help

Brett_Bleess — Tue, 11 Aug 2020 18:31:42 GMT

I believe this is going to require Set Analysis, so the following posts should put you on the right track in that case:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Regards,
Brett

Re: Straight Table Expression Help

Saravanan_Desingh — Wed, 12 Aug 2020 02:59:31 GMT

Can you upload your data in Excel format?