topic Re: Urgent - displaying values based on condition in QlikView

Urgent - displaying values based on condition

sayadutt — Thu, 14 Aug 2014 08:05:49 GMT

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

Re: Urgent - displaying values based on condition

tresB — Thu, 14 Aug 2014 08:11:36 GMT

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.

Re: Urgent - displaying values based on condition

ashfaq_haseeb — Thu, 14 Aug 2014 08:13:05 GMT

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

Re: Urgent - displaying values based on condition

sayadutt — Thu, 14 Aug 2014 10:08:41 GMT

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

Re: Urgent - displaying values based on condition

ashfaq_haseeb — Thu, 14 Aug 2014 10:12:50 GMT

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

Re: Urgent - displaying values based on condition

sayadutt — Thu, 14 Aug 2014 10:41:30 GMT

No Luck Ashfaq.

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

Re: Urgent - displaying values based on condition

ashfaq_haseeb — Thu, 14 Aug 2014 10:53:37 GMT

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

Re: Urgent - displaying values based on condition

MarcoARaymundo — Thu, 14 Aug 2014 13:04:06 GMT

Hi!

Look my model in attached file.

Att,

Marco

Re: Urgent - displaying values based on condition

sayadutt — Thu, 14 Aug 2014 14:03:08 GMT

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 !!!

Re: Urgent - displaying values based on condition

MarcoARaymundo — Thu, 14 Aug 2014 14:29:47 GMT

Change this

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

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