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

[#1]

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

[kaushiknsolanki]

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

[#3]

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

[kaushiknsolanki]

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

[#5]

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

[kaushiknsolanki]

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

[#7]

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

[kaushiknsolanki]

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

[#9]

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