Load table

[#1]

Hi,

I have the following text file;

---

Product No Exc/obs Value 0-90 Days 90-180 Days 180-360 Days >360 Days

A 50 40 0 10 0

B 15 0 15 0 0

C 100 25 25 25 25

---

I want the result to be;

0-90 Days 65

90-180 Days 40

180-360 Days 35

>360 Days 25

---

How do I load this text file in order to get the desired resulting table?

Thanks,

Jonas

[#2]

Hi Jonas,

Try something like this:

table1:

load ProductNo,

sum([0-90 Days]) as [0-90 Days],

sum([90-180 Days]) as [90-180 Days],

sum([180-360 Days] as [180-360 Days],

sum([>360 Days]) as [>360 Days]

from ....

group by ProductNo;

hope this helps

Regards!

[#3]

I would use crosstable.

Something like..

CROSSTABLE ([Days],[Amount],2)

(normal load statement here)

That will load up four columns:

Product No, Exc/obs Value, Days, Amount

My syntax might be a little off or I might have given your fields bad names but hopefully you get the idea.

Then you do [Days] as dimension and sum([Amount]) as the expression.

[#4]

In this case you have to store the table in a qvd and load it again in order to use the sum fuction

table1:

CROSSTABLE ([Days],[Amount],2)

(normal load statement here)

store table1 into table1.qvd;

drop table table1;

table2:

load ProductNo,

Days,

sum(Amount) as Amount

from table1.qvd

group by ProductNo, Days;

Regards!