Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Straight Table Expression Help

Hi

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

Labels (1)
4 Replies
Highlighted
MVP
MVP

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

Highlighted
Contributor III
Contributor III

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

 

 

 

 

Highlighted
Digital Support
Digital Support

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/...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
Specialist III
Specialist III

Can you upload your data in Excel format?