Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Cross Table


I evreyone

I have a script below which creates one table.

Staff:

Load  
    BIUNIQUEID,
    FIRSTNAME AS "First Name",
    GUID,
    JOBGRADE AS "Job Grade",
    LASTNAME AS "Surname",
    NABFULLNAME AS "Lotus Notes Name",
    NABCOMMONNAME AS "Full Name",
    "PWC_SERVICE_LINE" AS LoS,
    "PWC_SERVLINE_SPLTY" AS "Department",
    Floor ("RE_DAY") as "Termination Date",
    Year("RE_DAY") AS Year,
    Month("RE_DAY") as Month;

SQL SELECT *
FROM EMPLOYEES",


//DATES CALCULATIONS
Left Join
Load
    BIUNIQUEID,
    if("Termination Date"<=today(),1,0) as Terminated,
     InMonth("Termination Date", today(), 0)as Terminate_ThisMonth,
     InMonth("Termination Date", today(), 1) as Terminate_NextMonth,
     InMonth("Termination Date", today(), 2) as Terminate_InTwoMonths,
     InMonth("Termination Date", today(), 3) as Terminate_InThreeMonths
Resident STAFF;

I now want to use a CrossTable funtion to combine "Date Calculation" fields into one field so that I can use it in a listbox. I am struglling to get it right.

Please help

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Cross Table

Load

[Termination Date],

If([Termination Date] <= Today(), 'Terminated',

  If([Termination Date] <= AddMonths(Today(),1), 'Current Month',

  If([Termination Date] <= AddMonths(Today(),2), 'Next Month',

  If([Termination Date] <= AddMonths(Today(),3), 'In 2 Months',

  If([Termination Date] <= AddMonths(Today(),4), 'In 3 Months','More than 3 Months'))))) as Flag

Inline

[

  Termination Date

  01/10/2014

  15/11/2014

  25/12/2014

  20/01/2015

  25/02/2015

];

By changing 1,2,3,4 you can change the definition of Next Month, In 2 Months, In 3 Months etc...

Flag is the List Box Field you are requiring...

View solution in original post

5 Replies
Highlighted
MVP
MVP

Re: Cross Table

Load

[Termination Date],

If([Termination Date] <= Today(), 'Terminated',

  If([Termination Date] <= AddMonths(Today(),1), 'Current Month',

  If([Termination Date] <= AddMonths(Today(),2), 'Next Month',

  If([Termination Date] <= AddMonths(Today(),3), 'In 2 Months',

  If([Termination Date] <= AddMonths(Today(),4), 'In 3 Months','More than 3 Months'))))) as Flag

Inline

[

  Termination Date

  01/10/2014

  15/11/2014

  25/12/2014

  20/01/2015

  25/02/2015

];

By changing 1,2,3,4 you can change the definition of Next Month, In 2 Months, In 3 Months etc...

Flag is the List Box Field you are requiring...

View solution in original post

Highlighted
MVP
MVP

Re: Cross Table

Or

Load

[Termination Date],

IF([Termination Date] <= Today(), 'Terminated',

     IF(InMonth([Termination Date],Today(),0),'This Month',

          IF(InMonth([Termination Date],Today(),1),'Next Month',

                    IF(InMonth([Termination Date],Today(),2),'In Two Month',

                              IF(InMonth([Termination Date],Today(),3),'In Three Month','More Than 3 Months'))))) as Flag2

Inline

[

  Termination Date

  01/10/2014

  15/11/2014

  25/12/2014

  20/01/2015

  10/12/2014

  25/02/2015

  18/03/2015

  25/05/2015

  01/01/2015

  01/12/2014

  01/11/2014

];

Flag2 is the field you need to use in List Box

Highlighted
Specialist II
Specialist II

Re: Cross Table

if you strucked here then you need to do one time work here (creting one time QVD)

first create QVD of the file and load that qvd do cross table /transform whatever

after getting cross table script now replace 'from qvd path' in crosstable with "resident staff"

crosstble(....)

Load .

.

.

.

from qvd;// remove this line and replace with resident

Highlighted
Not applicable

Re: Cross Table

Thanks so much

Works much better than what I wanted to do

Highlighted
Not applicable

Re: Cross Table

Thanks for your assistance. You have saved me a lot of time indeed.