topic Re: How would I go about combining different fields in this certain way.. in QlikView

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

Wed, 06 Jul 2011 19:24:12 GMT

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.

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

Wed, 06 Jul 2011 19:30:17 GMT

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 ....

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

johnw — Thu, 07 Jul 2011 00:10:55 GMT

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.

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

Thu, 07 Jul 2011 13:42:10 GMT

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.

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

johnw — Thu, 07 Jul 2011 17:27:50 GMT

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.