topic Problem with pairing of data fields in QlikView

Problem with pairing of data fields

Wed, 25 Nov 2009 23:39:57 GMT

Hi,

I am just setting up a qlikview system that compares budgets with live financial information, however I have come across a problem.

One table that I pull data from displays information in a period by period basis with a layout like this:

P1 , P2, P3, P4 etc

When I load this table I put in the Crosstable command to put all of these periods into one column called Period.

The second table I use already has the Periods in one column, So I thought by having them both called Period they would match up nice and easily, e.g. P1, P2 , P3 for both however what I get instead is this:

etc

With both tables having a seperate line of Period values in the same column, any idea why?

Cheers Ben

Problem with pairing of data fields

prieper — Thu, 26 Nov 2009 00:36:18 GMT

Are you using a straight table? Which are the other fields shown there? Can you post a sample?

Peter

Problem with pairing of data fields

Thu, 26 Nov 2009 16:23:50 GMT

Hi, I am using a pivot table just because its the most user friendly way to have it but the same occurs with a straight table, please see attached screenshot of the actual problem. Here is a demo of the sort of code used to load the data too:

Budget_Live:

CROSSTABLE ([Period], [Budget Amount], 5)

LOAD

GBAID as [Account ID],

GBFY as [Fiscal Year],

GBBORG as [Budget YTD],

GBMCU as [Cost Centre],

GBOBJ as [Object Account],

GBAN01 as 1,

GBAN02 as 2,

GBAN03 as 3,

GBAN04 as 4,

GBAN05 as 5,

GBAN06 as 6,

GBAN07 as 7,

GBAN08 as 8,

GBAN09 as 9,

GBAN10 as 10,

GBAN11 as 11,

GBAN12 as 12,

GBAN13 as 13;

SQL SELECT *

FROM TRDTA.F0902;

And then the second table load looks like this:

Purchase_Order:

LOAD

// PDKCOO as [Co Order No],

PDDOCO as [Purchase Order No],

// PDLNID as [Line No],

PDAN8 as [Address No],

// PDDRQJ,

date(MakeDate(1900+left(text(PDDRQJ),len(PDDRQJ)-3))+right(text(PDDRQJ),3)-1) as [Date Requested],

// PDTRDJ,

// PDPDDJ,

date(MakeDate(1900+left(text(PDPDDJ),len(PDPDDJ)-3))+right(text(PDPDDJ),3)-1) as [Promised Delivery Date],

// PDOPDJ,

// PDADDJ,

date(MakeDate(1900+left(text(PDADDJ),len(PDADDJ)-3))+right(text(PDADDJ),3)-1) as [Actual Delivery Date],

// PDCNDJ,

date(MakeDate(1900+left(text(PDCNDJ),len(PDCNDJ)-3))+right(text(PDCNDJ),3)-1) as [Cancel Date],

date(MakeDate(1900+left(text(PDDGL),len(PDDGL)-3))+right(text(PDDGL),3)-1) as [Date for GL and Voucher],

PDNXTR as [Status Code Next],

PDLTTR as [Status Code Last],

PDAEXP as [Amount Extended Price],

PDAOPN as [Amount Open],

PDAREC as [Amount Received],

PDARLV as [Amount Relieved],

// PDFY as [Financial Year],

// PDAID as [Account ID],

PDOMCU as [Cost Centre],

PDOBJ as [Object Account],

PDPN as [Period];

SQL SELECT *

FROM TRDTA.F4311;

With the crosstable naming the 13 fields as Period and the last column in the second table load being called Period and containing 13 fields I thought the two would match up.

Does that help more?

Cheers

Ben

Problem with pairing of data fields

Anonymous — Thu, 26 Nov 2009 16:43:13 GMT

Looking at the screenshot I'd say some values are interpreted as text and others numerically.

Try adding a num() around the text values (the ones aligned to the left) and you should be good.

Problem with pairing of data fields

Thu, 26 Nov 2009 17:04:58 GMT

Hi thanks for that, I think you're right but I don't know how to put the Num () in, where do they go? I have tried putting them into the load screen like so:

Num (GBAN01) as 1,

Num (GBAN02) as 2,

Num (GBAN03) as 3,

Num (GBAN04) as 4,

Num (GBAN05) as 5,

Num (GBAN06) as 6,

Num (GBAN07) as 7,

Num (GBAN08) as 8,

Num (GBAN09) as 9,

Num (GBAN10) as 10,

Num (GBAN11) as 11,

Num (GBAN12) as 12,

Num (GBAN13) as 13;

But this doesn't seem to fix things, where do I put them?

Cheers

Ben

Problem with pairing of data fields

Thu, 26 Nov 2009 21:31:49 GMT

Cheers, that worked well once I understood! I couldn't use the Num () becuase those fields were then used in a crosstable, however I changed the others to TEXT() and it worked! Thankyou!