topic Re: Cross Table in QlikView

Cross Table

Tue, 11 Nov 2014 17:24:00 GMT

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

Re: Cross Table

MK_QSL — Tue, 11 Nov 2014 17:43:29 GMT

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

Re: Cross Table

MK_QSL — Tue, 11 Nov 2014 17:59:13 GMT

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

Re: Cross Table

preminqlik — Tue, 11 Nov 2014 18:02:48 GMT

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

Re: Cross Table

Tue, 11 Nov 2014 18:06:50 GMT

Thanks so much

Works much better than what I wanted to do

Re: Cross Table

Tue, 11 Nov 2014 18:11:52 GMT

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