Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MK_QSL
MVP
MVP

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
MK_QSL
MVP
MVP

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

MK_QSL
MVP
MVP

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

preminqlik
Specialist II
Specialist II

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

Not applicable
Author

Thanks so much

Works much better than what I wanted to do

Not applicable
Author

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