topic Combining data fields in a crosstable load - drops an attribute field. in QlikView

Combining data fields in a crosstable load - drops an attribute field.

mazacini — Wed, 07 Mar 2012 09:16:34 GMT

I have 2 attribute columns, and 4 data columns.

I want to combine the 4 data columns into 2 new columns, each contaiining 2 of the old columns.

Crosstable (SalesItem,Data,2)

LOAD Site,

Month,

ColA + ColB as Item1,

ColC + ColD as Item2

FROM etc

The problem is that it does not seem to load Month.

When I do a straight Crosstable load, as follows, I don't have a problem (but I have too many data types)

Crosstable (SalesItem,Data,2)

LOAD Site,

Month,

ColA,

ColB,

ColC,

ColD

FROM etc

Any ideas?

Combining data fields in a crosstable load - drops an attribute field.

CELAMBARASAN — Wed, 07 Mar 2012 09:38:31 GMT

Hi,

Can you upload sample data and exact script you used?

Celambarasan

Combining data fields in a crosstable load - drops an attribute field.

mazacini — Wed, 07 Mar 2012 10:28:16 GMT

Hi Celambarasan

Thank ou for your help. As data was sensitive, I recreated the scenarion using test data, and it worked!

So this made me look elsewhere for the problem.

I had added data to the table manually for a new Month, and had left some of the fields blank (ie ColA and ColC were blank).

That is why the New Month did not load.

Once I entered data (or 0, but not '-'), everything was ok?

If you can advise what the rules are about blanks (or '-', that would be great)? Is there something I can do in the load to ignore the situation where there are blanks ie to treat as 0?

Anyway, thanks for your help.

Joe

Re: Combining data fields in a crosstable load - drops an attribute field.

CELAMBARASAN — Wed, 07 Mar 2012 10:32:20 GMT

Hi,

Then use Alt function which gives you zero if it is null like below.

Alt(ColA,0)+Alt(ColB,0) as Item1

Or try with range sum

RangeSum(ColA,ColB) as Item1

Hope it helps

Celambarasan