Straight Table Expression Help

[Ankhi]

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

[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]

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]

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

[Saravanan_Desingh]

Can you upload your data in Excel format?