join

Tue, 28 May 2013 15:52:32 GMT

Hello,

I have this table:

End_User: ( name, order_date,....)

I wanna create a table from this one which contains End-users who have order_date=2013 and order_date=2012

which means End-users who made orders in 2012 and 2013

How can I do that?

Re: join

RedSky001 — Tue, 28 May 2013 15:55:20 GMT

Google "master calendar qlikview"

Re: join

Tue, 28 May 2013 15:57:09 GMT

Hey,

Just put this in the load script on its own tab

//     ************
MinMax Table ****************

MinMax:

       LOAD

             Min(OrderDate)           AS MinDate,

             Max(OrderDate)           AS MaxDate

       RESIDENT


Orders;


//
************ Create Min and Max Variables ****************
      LET vMinDate = NUM(PEEK('MinDate', 0,
'MinMax'));

       LET vMaxDate = NUM(PEEK('MaxDate', 0,
'MinMax'));

       LET vToday   = $(vMaxDate);

//
************ Temporary Calendar ****************

TempCal:

       LOAD

             DATE($(vMinDate) + rowno()
-1)         AS TempDate

       AUTOGENERATE

             $(vMaxDate) - $(vMinDate) + 1;



       DROP TABLE MinMax;

//
************ Master Calendar ****************

MasterCalendar:

       LOAD

             TempDate                              AS OrderDate,            //Join
to Orders Table
            WEEK(TempDate)                        AS Week,

             YEAR(TempDate)                        AS Year,

             MONTH(TempDate)                       AS Month,

             DAY(TempDate)                         AS Day,

             WEEKDAY(TempDate)                     AS WeekDay,

             'Q'
& CEIL(MONTH(TempDate) /
3)        AS Quarter,

             DATE(MONTHSTART(TempDate),'MMM-YYYY') AS MonthYear,

             WEEK(TempDate) & '-' &Year(TempDate) AS WeekYear,

             INYEARTODATE(TempDate,$(vToday),0) * -1 AS CurYTDFlag,

             INYEARTODATE(TempDate,$(vToday),-1)* -1 AS LastYTDFlag

       RESIDENT


TempCal

       ORDER BY

             TempDate ASC;



       DROP TABLE TempCal;

Re: join

Wed, 29 May 2013 09:59:17 GMT

ok thanks

Re: join

er_mohit — Wed, 29 May 2013 10:16:46 GMT

See the attache file

hope it helps

topic join in QlikView

join

Re: join

Re: join

Re: join

Re: join