Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mazacini
Creator III
Creator III

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

Hi

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?

3 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Can you upload sample data and exact script you used?

Celambarasan

mazacini
Creator III
Creator III
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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