Generating a dimension based on multiple fields in a table

[#1]

I have a table in my data model like so:

Id Book Issuer 1w 1m 2m 3m 6m 9m 1y 2y
2514522 L1491 L1002827 0 0 0 0 0 0 0 0
2514522 L1491 L1051485 0 -82 -136 0 0 0 0 0
...

I now need a straight table that has as its dimension 'Tenor', where Tenor has the values 1w, 1m, 2m, 3m, 6m, 9m, 1y, and 2y. The plan is to do an aggregations in the straight table like summing all the values per Tenor bucket for a given selection. Note that the table is very large, so I cannot transform the table itself.

Any suggestions are appreciated!

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

As far as I see it, it's the classic example for a crosstable load:

Data:CROSSTABLE (Tenor, Values, 3) LOAD *RESIDENT Table; // This is your original loaded table DROP TABLE Table;

I know that it's in the script, byt it'd give it a try.

Hope that helps

[#3]

Actually, I'm trying to avoid Crosstable. Sorry, should have mentioned that. The table is quite big and in fact there are not 8 but 20 Tenor values - that's the reason the data is supplied like this in the first place. Any other suggestions?

[johnw]

Since your original table and the crosstable result contain essentially the same information content, and since QlikView compresses your data, I would expect both versions to be similar in size.

Or by "I cannot transform the table" are you referring to how a crosstable load uses a significant amount of memory while it is in progress, even if that memory drops back down when it is done? You could probably partition out the work, like doing ten separate crosstables and concatenating them all as you go.

But that wasn't the question you asked, so let's move on.

My suggestion would be to simply make a different expression for each Tenor rather than having a Tenor dimension. If your data set is huge enough to cause trouble with crosstable, I think it's huge enough to cause trouble with most Tenor-dimension-based solutions other than crosstable.

[#5]

OK, I tried the CrossTable anyways, and although it increases the load time, it should be fine.