topic Field should come as a table in QlikView

Field should come as a table

Thu, 19 Mar 2015 04:28:50 GMT

Dear All,

i have a excel table in that 30 fields are there. when i load the same into qlikview then i ll get a single table in Data model.

but in my case i want all that 30 fields into a individual table in data model (30 seperate tables - Each table consists of Key field and field) with the fact table in it. (like star schema) .

Also if one more field in excel added in future that too should get added in data model as a table.

Re: Field should come as a table

sujeetsingh — Thu, 19 Mar 2015 04:47:30 GMT

Use resident load in the script:

Main Data:

Load

Field1,Autohash(Field1) as Key1

,Field2' ,Autohash(Field2) as Key2

Field30,Autohash(Field30) as Key30

from file xls;

Table1:

Load

Key1

,Field1 as First_Field

resident MainData;

Table2:

Load

Key2

,Field2 as Second_Field

resident MainData;

---

Similarly for all 30

Re: Field should come as a table

Thu, 19 Mar 2015 05:43:17 GMT

Hi Logesh:

I think you can use metadata to extract all the names of the fields you want to make individual tables.

- I am not sure if you can read metadata of an excel file in Qlikview directly but I am sure you can read metadata from QVD files

- And then you can filter out the Key fields and keep the fields you want to make individual tables

Concatenate them using CONCAT() with single quotes and comma.

-Something like CONCAT(chr(39) & [Field Names] & chr (39),',') AS [Concat Field Names]

And then use FOR EACH ... IN ... to loop through all the names and create a single table in each loop.

- Something like

FOR EACH vField IN $(Concat Field Names)

[$(vField)]:

NoConcatenate

LOAD

[Key Field 1],

[Key Field 2],

...

[$(vField)]

FROM ...

I think in this case, even if you add in new fields the new table will generate automatically.

Above scripts may not be exact and I haven't test it before. Just a suggest on top of my head

Thanks.

Re: Field should come as a table

Thu, 19 Mar 2015 05:47:32 GMT

Actually I am not sure how is your Fact table would look like.

If you need multiple key fields for individual tables, above script will create synthetic fields. So maybe concatenate them instead of what I just wrote.

So instead of using

[Key Field 1],

[Key Field 2]

...

You can use

[Key Field 1] & '-' & [Key Field 2] & '-' & [Key Field 3]...

Re: Field should come as a table

hariprasadqv — Thu, 19 Mar 2015 06:07:38 GMT

No automatic loading and data trasformation is available. I thinkl. you can try ETL tool of the QV in data loading. Or you can harcod it like sujeet's approach.