Urgent - displaying values based on condition

[sayadutt]

Hi Guys,

I have 2 fields, Associate name and a date field called as 'Project End Date', which contains some dates.

Now my requirement is to display only the name of the associates where Project End Date is past date (less than today)

for eg:

Associate  Project End Date

A                08/08/2014

A                08/20/2014

B                08/08/2014

B                08/12/2014

So, it should display only associate B name as he don't have any active projects.

For A, there is 1 active project, so A's name should not be displayed.

Can you please help here.

Thanks

[tresesco]

Simple condition: If([Project End Date]=Today()-1, Associate)

Now, you have to take care of it based on your requirement-where/how you want the output.

[ashfaq_haseeb]

Hi,

Try below

Temp:

load * Inline

[

Associate,ProjectEndDate

A ,08/08/2014

A,08/20/2014

B,08/08/2014

B,08/12/2014

];

Main:

Load *

where DATE <= date(today());

Load Associate,max(date(ProjectEndDate)) as DATE

Resident Temp

group by Associate;

Drop table Temp;

Regards

ASHFAQ

[sayadutt]

Hi Ashfaq,

When I tried your above example, it works and this is what I am looking for.

But when I am trying to incorporate the same in my existing code, it fails. Can you please let me know what I did wrong.

main:

LOAD

     [03. Project Name  ] as [Project Name  ],

     [04. Release No  ] as [Release No  ],

     [07. Associate Working On  ] as [Associate Working On  ],

     [09. QA End Date  ] as [QA End Date  ]   

FROM

[<Sharepoint path>]

(html, unicode, embedded labels, table is @4);

Tab23:

NOCONCATENATE

LOAD Distinct SubField([Associate Working On  ],'; ') as [Single Associate Working On],

    [Project Name  ] , [Release No  ]

Resident main;

Load *

where DATE <= date(today());

Load [Single Associate Working On],max([QA End Date  ]) as DATE

Resident Tab23

Group by [Single Associate Working On];

[ashfaq_haseeb]

Try Below

main:

LOAD

     [03. Project Name  ] as [Project Name  ],

     [04. Release No  ] as [Release No  ],

     [07. Associate Working On  ] as [Associate Working On  ],

     [09. QA End Date  ] as [QA End Date  ] 

FROM

[<Sharepoint path>]

(html, unicode, embedded labels, table is @4);

Tab23:

NOCONCATENATE

LOAD Distinct SubField([Associate Working On  ],'; ') as [Single Associate Working On],

    [Project Name  ] , [Release No  ],[QA End Date  ]

Resident main;

Load *

where DATE <= date(today());

Load [Single Associate Working On],max([QA End Date  ]) as DATE

Resident Tab23

Group by [Single Associate Working On];

[sayadutt]

No Luck Ashfaq.

Doing above, on Reload its shows a Pop Up "Execution of script failed. Reload Old data??"

[ashfaq_haseeb]

Hi,

You need to tweek your code.

Can you monitor the same by enabling Document log from Setting --> Document Properties --> Select Generate Log File.

Regards

ASHFAQ

[MarcoARaymundo]

Hi!

Look my model in attached file.

Att,

Marco

[sayadutt]

Hi Marco,

My requirement is not to show A at all since he has 1 active project (20/8). Can you please give me the modified file.

Also C should come in the result, as there is no active project for him (no project at all)

So the Status section should look like this:

Associate  B   08/08/2014  80%

                      12/08/2014  85%

Associate C        --             --

Can you please help here.

Thanks in advance !!!   

[MarcoARaymundo]

Change this

Expression 1 Sum({<Project_End_Date={'>$(=Today())'}>} Status)

Expression 2 Sum({<Project_End_Date={'>$(vEndDate)'}>} Status)