Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

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
Kushal_Chawda

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

Ankhi
Creator
Creator
Author

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

 

 

 

 

Brett_Bleess
Support (Former)
Support (Former)

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

Can you upload your data in Excel format?