topic Datamodel in QlikView

Datamodel

NavinReddy — Fri, 21 Mar 2014 18:08:17 GMT

Deal all,

i have two excels one is 2013 data another one is 2014 data,

when i am loading im getting synthatic keys,but both the excel same fields

could you please provide me some best Datamodel

please avoid synthatic keys please help me

please find the attechment

Thanks In Advance

Niranjan

Re: Datamodel

aveeeeeee7en — Fri, 21 Mar 2014 18:15:10 GMT

Hi Niranjan

Use Concatenate function between your 2 Tables

and to identify the data coming from which Table use Flag

Use the below Script:

LOAD GID,

[Standard Id],

[Employee Full Name],

[Salary Grade Id],

[Loc City Name],

[Jobcode Id],

[Jobcode Desc],

[Pay Company Id],

[Pay Company Name],

[Pay Check Department Id],

[Pay Check Department Name],

[Pay CSH Hrcy Lvl 5 Name],

[Pay CSH Hrcy Lvl 6 Name],

[Pay CSH Hrcy Lvl 7 Name],

[Pay CSH Hrcy Lvl 8 Name],

[Pay CSH Hrcy Lvl 9 Name],

[Pay CSH Hrcy Lvl 10 Name],

[Pay CSH Hrcy Lvl 11 Name],

[Pay Yr & Month],

PAY_HRS,

PAY_AMT,

[Level 1],

[Level 2],

[Level 3],

'2014' AS Flag

FROM

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD Month,

GID,

[Standard Id],

[Full Name]as [Employee Full Name,

[Salary Grade Id],

[Loc City Name],

[Jobcode Id],

[Jobcode Desc],

[Pay Company Id],

[Pay Company Name],

[Pay Check Department Id],

[Pay Check Department Name],

[Pay CSH Hrcy Lvl 5 Name],

[Pay CSH Hrcy Lvl 6 Name],

[Pay CSH Hrcy Lvl 7 Name],

[Pay CSH Hrcy Lvl 8 Name],

Group,

[Pay CSH Hrcy Lvl 10 Name],

[Pay CSH Hrcy Lvl 11 Name],

[Pay Yr & Month],

[MTD Hrs]as PAY_HRS,

[MTD $]as PAY_AMT,

[LOB -Level 4],

[Level 5],

[Sub-Group],

Function,

Direct,

Manager,

2013' AS Flag

FROM

(ooxml, embedded labels, table is Details);

After writing the Script, do Reload

Add a Table Box on your sheet and bring all the fields in it. After doing this, on the sheet bring the field Flag and select 2014 & 2013 years from it.

The data will filter out in the Table Box as per your Selection.

Hope that works for you

Regards

Aviral Nag

Re: Datamodel

Fri, 21 Mar 2014 18:20:31 GMT

If the two tables have the same fields then just do this.

Load *

From

(ooxml, embedded labels, table is Details);

If you look at the from command I used Data* which means in the future if you get 2015,2016,.... data you dont have to again go and recode it. It will automatically read from all the tables that begin with Data and do an Auto concat if the field names are the same.

Thanks

Re: Datamodel

NavinReddy — Fri, 21 Mar 2014 18:49:28 GMT

Thanks you gus for your help full suggessions

Re: Datamodel

shree909 — Fri, 21 Mar 2014 19:54:27 GMT

Hi,

To identify the tables

use

filebasename() AS KEY.

For each table.

when u load and concatenate,

Use KEY as a list box .

Re: Datamodel

its_anandrjs — Fri, 21 Mar 2014 19:59:49 GMT

Hi,

You can load your excel file some thing like below and keep this all file in any folder and be insure its name format is same with each other and also field name is same. Then load your excel tables with only one load script like below

TableName:

LOAD *,

filebasename( ) as Filename,

Right(filebasename( ),4) as YearFlag

FROM

(ooxml, embedded labels, table is Sheet1);

And you see after its name Data*.xlsx is used means file name start with Data and followed by date string and also you are able to capture its Filename with filebasename( ) and extract Year also for flaging.

Hope this helps

Thanks & Regards

Re: Datamodel

shree909 — Fri, 21 Mar 2014 20:38:48 GMT

a small change to the flag,

If u want to dynamically assign Year values,

use subfield(Filebasename(),'-'-1) AS Flag.

I know the above one works but u need to hard code every time when u get a new year,

Thanks'