topic Adding fields to source as months go by in QlikView

Adding fields to source as months go by

vvvvvvizard — Mon, 22 Jun 2015 08:25:52 GMT

Quite a silly question

Each month i load in am excel file containing Column A , B , C for month 1 , 2 , 3 ...

as the months pass on by , sometime additional columns get added A , B , C ,D E , month 4 , 5 , 6

Is it possible to load all of these with one load statement . I currently have one load statement for month 1 , 2, 3 and then an outer Join statement to table to load month 4, 5, 6 and each time new columns get added , i have a new outer join and a new load statement ect ect ect , now there's just to many outer joins and loads

How can i load them all with one Load statement .

Re: Adding fields to source as months go by

jagan — Mon, 22 Jun 2015 08:37:50 GMT

Hi,

Instead of specifying columns in the Load statement, directly use * in the load statement so that all columns will be loaded.

Example:

LOAD

FROM FileName.xls;

Hope this helps you.

Regards,

Jagan.

Re: Adding fields to source as months go by

jonathandienst — Mon, 22 Jun 2015 08:43:52 GMT

Data like this should be loaded with a cross table load- this will give two fields - a month (as data), and a value -- rather than a field for each month. You have already encountered some of the problems with the latter approach.

Load something like this:

Crosstable(Month, Value, <n>)

LOAD *

FROM .......xlsx

(ooxml, ....);

(replace <n> with the number of attribute columns before the first month column). The results table will contain the <n> attribute fields, a Month field (which will contain the column header in the spreadsheet) and a Value field. The * in the load means it will load whatever months are in the file.

Re: Adding fields to source as months go by

vvvvvvizard — Mon, 22 Jun 2015 08:54:24 GMT

well then you get many tables with lots of sythetic keys , the source files with extra columns get loaded as Table1 , those with more columns as Table 2 , table 3 , ect

Try

Table1:

Load *

with 2 excel sheets , one with columns A , B and other with A, B, C

you get 2 tables instead of one ,

Re: Adding fields to source as months go by

vvvvvvizard — Mon, 22 Jun 2015 09:00:00 GMT

Apologies for not giving enough info , i currently get the date field from the file name of the excel worksheet . All the data in the worksheet is in a tabular format (not in cross table format) .

Re: Adding fields to source as months go by

datanibbler — Mon, 22 Jun 2015 11:39:41 GMT

Hi,

I have this exact issue in an app which I built a while ago. Every month, one column is added. My solution:

- First I do a FIRST 1 LOAD with the * so that I get all the names of all the fields.

- I have a loop starting at the first row which I am not certain about anymore and counting from there the number
of months still to come (say it runs from 37 to (37+8=)44)

- In the LOOP, the names of the fields are identified and

- I query whether they begin with some letters leading me to believe there is useful data in there

- I have a variable reading like > v_field_37 < and one like > AS_v_field_37 <

          - If they DO contain these letters, then v_field_37 is filled with the actual name and AS_v_field_37 with
            a logical alias_name
            <-> otherwise, both are filled with the text "dummy_37"

- Then comes the actual LOAD where I load all these fields, whether there is useful monthly_data in there or
not.

- After the LOAD, I have another LOOP where I parse all the fields in the result_table again, beginning at the first one
I'm not sure about anymore and again counting up for all the months to come
=> When the field_names start with "dummy", I just delete them again.

That's it in short. You'll have to do quite some trying out and testing. So did I, that thing with the LOOPs is not exactly easy.

HTH

Re: Adding fields to source as months go by

vvvvvvizard — Mon, 22 Jun 2015 12:00:22 GMT

Sounds like a plan , il test and advise