How would I go about combining different fields in this certain way..

[#1]

Hi,

I need to create a table for CSV export at the end of the script, and I have fields from different tables that I need to combine, but not just glue the fields together. Here is what I mean:

SomeField | Year  | Month | Number

        1        | 2008 | Jan       | 10

        2        | 2008 | Jan       | 14

        3        | 2008 | Jan       | 12

...........

        1        | 2009 | Jan       | 31

        2        | 2009 | Jan       | 24

.........

I hope you get the pattern.

So my problem is: I need to make sort of a crosstable, where i have the months as fields (so the fields would be: SomeField, Year, Jan, Feb, Mar,...) and the the Number would be a value for a particular month.

I have no idea how to go about forming this table. Any help is appreciated.

Thank you,

Siarhei K.

[#2]

To add some more detail to what I need to get as the result is the following:

The rows from the example would be

1     2008     10(this is Jan)     12(this is Feb)     11(this is Mar)......

2     2008     14 ....

3     2008     12 ....

....

1     2009     31 ....

2     2009     24 ....

[johnw]

Have a look at "Generic Load".  Unfortunately, a generic load actually makes a whole bunch of tables with the same key, one field each.  To combine them, you could write a loop to left join each new field onto a single table and drop the associated table.  I have an example of the generic load, but not of combining into a single table.  I've SEEN one, though, so perhaps you can find it with a forum search.  If not, I can either find it or create my own example.

[#4]

Thank you, John, that gave me some ideas. However, the way I'm thinking of solving my problem would be veeeery slow, O(n^4), which depends on the number of fields I have before the cross-tab switch (when the months' fields start).

Thanks again.

Siarhei K.

[johnw]

I've updated the example to include joining all of the tables into one.

[Final]:
LOAD DISTINCT "Invoice No." // add other key fields to this list
RESIDENT [Table]
;
FOR I = nooftables() TO 0 STEP -1
    LET vTable = tablename(I);
    IF  wildmatch('$(vTable)','Generic.*') THEN
        OUTER JOIN ([Final])
        LOAD *
        RESIDENT [$(vTable)];
        DROP TABLE [$(vTable)];
    END IF
NEXT

With that approach, I wouldn't expect the number of fields before the switch to be particularly significant.  I'm not sure what algorithm QlikView uses for a LOAD DISTINCT, but it certainly wouldn't be o(n^4).  I'd expect o(n log n) for that part.  We can also eliminate it if necessary (the comments in my example include a more complicated version without it, though I don't guarantee that it's faster).  But maybe that's not the aspect of solving your problem that you're talking about.