topic Re: Crosstable Field - Doubt to concatenate fields in QlikView

Crosstable Field - Doubt to concatenate fields

Thu, 03 May 2012 18:40:20 GMT

Good day,

My friends, i've got a crosstable as below:

KEY:

CROSSTABLE(Month, Data)

LOAD Location,

[01/01/2012],

[01/02/2012],

[01/03/2012],

[01/04/2012],

[01/05/2012],

[01/06/2012],

[01/07/2012],

[01/08/2012],

[01/09/2012],

[01/10/2012],

[01/11/2012],

[01/12/2012]

FROM [...];

The qualifying field is Location, but I really need to create a Location&Month field. (Month is not a qualifying field)

How can I create it?? I've tried to input like this below, but it shows an error that it didn't find the field:

Example:

KEY:

CROSSTABLE(Month, Data)

LOAD

Location,

Location&Month,

[01/01/2012],

[01/02/2012],

...

Hope you masters of QlikView can help me.

Kind rgds, Jorge.

Re: Crosstable Field - Doubt to concatenate fields

jonathandienst — Thu, 03 May 2012 19:53:03 GMT

Jorge

If I understand you correctly, the following should do what you need:

KEY:
LOAD *,
     Location&Month As LocMonth;
CROSSTABLE(Month, Data)
LOAD Location,
[01/01/2012],
[01/02/2012],
[01/03/2012],
[01/04/2012],
[01/05/2012],
[01/06/2012],
[01/07/2012],
[01/08/2012],
[01/09/2012],
[01/10/2012],
[01/11/2012],
[01/12/2012]
FROM [...];

Regards

Jonathan

Re: Crosstable Field - Doubt to concatenate fields

Thu, 03 May 2012 20:10:29 GMT

Jonathan, yes i already tried that.

The main problem is that the field Month is not a qualifying field.

Let me be more specific:

I have this table

Location	01/01/2012	01/02/2012	01/03/2012
ARBUE	10	12	15
BRITJ	5	5	20
UYMVD	10	10	12
USNEW	10	10	12

Then i did the crosstable to configure the first row as month, and i created the Location column as a qualifying field.

Now i need to concatenate these two fields, Location and Month, but Month is not a qualifying field from crosstable.

Understand??

Rgds

Jorge

Re: Crosstable Field - Doubt to concatenate fields

Fri, 04 May 2012 12:21:54 GMT

Come on people, i know that there is one single soul in this community, that probably know how to do that.

Re: Crosstable Field - Doubt to concatenate fields

jonathandienst — Fri, 04 May 2012 13:23:08 GMT

Jorge

I don't know what it is you are expecting from the load statement. If the source is in the format above, then the simple crosstable above should work for you.

But perhaps you are expecting something different? You may need some more detail and/or examples to illustrate your requirements.

Regards

Jonathan

Re: Crosstable Field - Doubt to concatenate fields

Fri, 04 May 2012 13:29:16 GMT

Jonathan,

Yes the crosstable worked for me, it gives me three fields right (Location, Month and Data - Location as unqualifying field).

I just want to know how can i concatenate these two fields Location and Month (Location&Month didn't work cause it didn't recognize the field Month).

Let me know if i'm not making myself clear.

Tks and rgds,

Re: Crosstable Field - Doubt to concatenate fields

jonathandienst — Fri, 04 May 2012 13:34:49 GMT

Jorge

If it does not work in the preceding load I posted earlier, then I would suggest doing a join after the cross table load to add the Location&Month key. Something like:

Join (DataTable)

LOAD Location,

Month,

Location & Month As LocMonth

Resident DataTable;

(change DataTable to the name of the table produced by the cross table load)

Regards

Jonathan