topic Re: need help for getting correct result for below query.... in QlikView

need help for getting correct result for below query....

Tue, 02 Apr 2013 06:02:07 GMT

Hi,
Help me for getting the result for following query.

LOAD *, Date(TODAY())

RESIDENT Table2

WHERE Interval(Date(closingdate)-Date(TODAY()) ,'YYYY-MM-DD' ) <=90 and

not WildMatch(sales_stage,'Closed Won','Closed Lost','Lead Dead')

order by closingdate;

I want the result in following manner..

1) result date should be displayed from current date.

2)it should display next 90 days result from current date.

the reult im getting by executing this query is not from current date but yes it is restricting dates exceeding next 90 days ....It is taking data from first....

Re: need help for getting correct result for below query....

kaushiknsolanki — Tue, 02 Apr 2013 06:09:52 GMT

Hi,

Do you mean that you need last 90 days data?

If yes then try below Script.

Load * from abc where num(Date) >= num(Today()-90) and wildmatch(Sales_Stage,'Closed Won','Close Lost','Lead Dead');

If not then kindly describe your problem with an example.

Regards,

Kaushik Solanki

Re: need help for getting correct result for below query....

Tue, 02 Apr 2013 06:38:13 GMT

Hi Kaushik,

I am executing below code for getting the result...
1)Table 2 - for getting next 90 days result from current date ( ex: if todays date is 2013-04-02 then result should be all dates between 2013-04-02 to 2013-07-02...ie next 90 days...

2)Table1 - snapshotdate will include all the data before snapshot date and also data of next 90 days
(my snapshot date is 2013-02-28 so result should come all the dates before snapshot date and plus next 90 days data)

ODBC CONNECT32 TO MSSQL (XUserId is CAfacZFMfA, XPassword is QdKEGYFMTbcAUYIGTRSB);

Table1:

select potentialid ,snapshotdate ,closingdate ,amount ,sales_stage

FROM Qlikview.dbo.snapshot;

NOCONCATENATE

Table2:

select potentialid,closingdate,amount,sales_stage

from Qlikview.dbo.potential;

NOCONCATENATE

FINAL:

LOAD *, Date(TODAY()) as todaysdate

RESIDENT Table2

WHERE Interval(Date(TODAY())-Date(closingdate) ,'YYYY-MM-DD' ) <=90 and

not WildMatch(sales_stage,'Closed Won','Closed Lost','Lead Dead')

order by closingdate;

Concatenate

LOAD * RESIDENT Table1

WHERE Interval(Date(closingdate)-Date(snapshotdate) , 'YYYY-MM-DD' )<=90 and

not WildMatch(snapshotdate,'')

order by closingdate;

Drop table Table1;

Drop table Table2;

Regards,
Hitesh

Re: need help for getting correct result for below query....

kaushiknsolanki — Tue, 02 Apr 2013 06:50:43 GMT

Hi,

For table 1 you can try below logic.

Load * from Table1 where Date >= today() +90;

And for Table 2 you can try this.

Temp:

Load Max(SnapshotDate) as Max from Tabl2;

Let vMax = num(peek('Max',0,'Temp'));

Load * from Table2 where Date >= today() +90 and Date <= $(vMax);

Regards,

Kaushik Solanki

Re: need help for getting correct result for below query....

Tue, 02 Apr 2013 08:30:07 GMT

Hi Kaushik,

PFA

I am not getting proper result so plz edit in code which i have mentioned earlier.......

i am also attaching the excel docs..

Re: need help for getting correct result for below query....

kaushiknsolanki — Tue, 02 Apr 2013 09:07:31 GMT

Hi,

Do you mean to say that you need a table which should have data from potential table for next 90 days and snap_shot table where the data should be less then snap_shot_date?

If not then kindly tell me from your example what output is expected

Regards,

Kaushik Solanki

Re: need help for getting correct result for below query....

Tue, 02 Apr 2013 09:48:37 GMT

I am trying to get the bar chart of following result...

1) x-axis : (todays date and snapshotdate)

2) Legends : sales_stage

PFA for result bar chart snap

Re: need help for getting correct result for below query....

kaushiknsolanki — Tue, 02 Apr 2013 10:02:01 GMT

Hi,

Hitesh I am bit confused about your requirement, I beg your purdon.

What I understood from your first post is that, you want a solution for fetching the proper data from source, but what your last post says is you need solusion for front end at chart level.

Kindly help me to clear my doubt, to solve your problem.

Regards,

Kaushik Solanki

Re: need help for getting correct result for below query....

Tue, 02 Apr 2013 10:30:57 GMT

Hi ,

Lets not create any confusion.
i want solution for fetching the proper data from source...
which i have posted earlier with the whole code which i'm trying to execute.

Regards,
Hitesh