Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mariasdu
Contributor

Loading two data sets with identical variable names

Hi

I want to use two different datasets in Qlikview - that have the same variable names and the difference between the datasets are, that they are compiled at different times of the year (one time a year and daily). I want to create a sheet in a Qlikview application where I show tables and graphs compiled daily and another sheet with (almost) the same tables and graphs compiled yearly.Qlikview failed in loading two different data sets with the same variable names, so therefore I renamed the variabes in the daily data set to have _dd after each variable name.

But I don't think it is nice to have a variable to appear År_dd (week_dd - dd means daily) in a pivot table, so I tried to rename the _dd variables as it can be seen beow. But when I do that, Qlikview can't seem to distinguish between the variables and make identical tables when I use the daily data set and the yearly data set - and it should not!

How can I use two data sets with the same variable names without Qlikview getting confused?

(if I in the script below don't write as [Fakultet] etc. in the "STAA_IDAG_DD", the tables are made just fine - and Qlikview can seperate the daily data fromthe yearly data)

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='kr. #.##0,00;kr. -#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;ti;on;to;fr;lø;sø';

STAA_KUM:
LOAD PERSON_ID,
    
rsj_besldato,
    
rsj_anledning,
    
rsj_lbnr,
    
rsj_ress_staa,
    
rsj_res_id,
    
rsj_sted_id,
    
stud_uddramme_id,
    
rsj_uddelement_id,
    
RETRES_KARAKTER,
    
bedodato,
    
staa_id,
    
korselsdato,
    
STUDIEORDN_ID,
    
STORDKOD,
    
STUDIERETNING as Studieretning,
    
EKA_KODE,
    
EKA_NAVN as Aktivitetsnavn,
    
BELASTNING,
    
ADMENHED as [Adm. enhed],
    
TEORI_PRAKTIK,
    
EKA_aktgrp,
    
aarugenr as Ugenummer,
    
ugenr,
    
STAA as [STÅ],
    
fakultet as Fakultet,
    
aar as [År]
FROM
[QV filer til STAA\STAA_KOMMULERET.QVD]
(
qvd);

STAA_IDAG_DD:
LOAD rsj_besldato_dd,
    
rsj_anledning_dd,
    
rsj_sted_id_dd,
    
stud_uddramme_id_dd,
    
RETRES_KARAKTER_dd,
    
bedodato_dd,
    
korselsdato_dd,
    
STUDIERETNING_dd as [Studieretning ],
    
EKA_NAVN_dd as [Aktivitetsnavn ],
    
BELASTNING_dd,
    
ADMENHED_dd as [Adm. enhed ],
    
TEORI_PRAKTIK_dd,
    
EKA_aktgrp_dd,
    
aarugenr_dd,
    
ugenr_dd as [Ugenummer ],
    
STAA_dd as [STÅ ],
    
fakultet_dd as [Fakultet ],
    
aar_dd as [År ],
    
person_id
FROM

(
ooxml, embedded labels, table is STAA_IDAG_DD);

1 Solution

Accepted Solutions
rupe
Valued Contributor

Re: Loading two data sets with identical variable names

I would keep the field names the same and use a concatenate load for the second table.

Importantly you should flag each set of data with a field such as Type  and use the value of "Daily" or "Yearly" for each data set. Then you can use identical sheets for each set of data with a list bar showing the term Daily of Yearly to define the datasets.

I hope this helps

9 Replies
rupe
Valued Contributor

Re: Loading two data sets with identical variable names

I would keep the field names the same and use a concatenate load for the second table.

Importantly you should flag each set of data with a field such as Type  and use the value of "Daily" or "Yearly" for each data set. Then you can use identical sheets for each set of data with a list bar showing the term Daily of Yearly to define the datasets.

I hope this helps

MVP
MVP

Re: Loading two data sets with identical variable names

I think Rupert suggests a good solution

but if you want 2 different sheets you can use trigger at the sheet level (properties --> trigger --> ...->   select in field and lock field) to have daily and yearly data in different sheets

mariasdu
Contributor

Re: Loading two data sets with identical variable names

Ok, thanks for the answer. Now my script looks like the one below. I have created a new variable called datakilde (datasource). The daily data have the value "daglig" (daily) in this new variable and the yearly data have the value "Aarlig" (yearly) in the new variable.

If I want to create a table with ONLY data from the daily data - where do I put this condition in the properties menu? And do I just write datakilde='Daglig' ?? (datasource ='Daily')

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='kr. #.##0,00;kr. -#.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;ti;on;to;fr;lø;sø';

NY_STAA_IDAG:
LOAD PERSON_ID,
    
rsj_besldato,
    
rsj_anledning,
    
rsj_sted_id,
    
stud_uddramme_id,
    
RETRES_KARAKTER,
    
bedodato,
    
korselsdato,
    
STUDIERETNING as Studieretning,
    
EKA_NAVN as Aktivitetsnavn,
    
BELASTNING,
    
ADMENHED as [Adm. enhed],
    
TEORI_PRAKTIK,
    
EKA_aktgrp,
    
aarugenr,
    
ugenr as Ugenummer,
    
STAA as [STÅ],
    
fakultet as Fakultet,
    
aar as [År],
    
datakilde
FROM
[QV filer til STAA\STAA_IDAG.QVD]
(
qvd);

concatenate(NY_STAA_IDAG)

NY_STAA_KUM:
LOAD PERSON_ID,
    
rsj_besldato,
    
rsj_anledning,
    
rsj_lbnr,
    
rsj_ress_staa,
    
rsj_res_id,
    
rsj_sted_id,
    
stud_uddramme_id,
    
rsj_uddelement_id,
    
RETRES_KARAKTER,
    
bedodato,
    
staa_id,
    
korselsdato,
    
STUDIEORDN_ID,
    
STORDKOD,
    
STUDIERETNING as Studieretning,
    
EKA_KODE,
    
EKA_NAVN as Aktivitetsnavn,
    
BELASTNING,
    
ADMENHED  as [Adm. enhed],
    
TEORI_PRAKTIK,
    
EKA_aktgrp,
    
aarugenr as Ugenummer,
    
ugenr,
    
STAA as [STÅ],
    
fakultet as Fakultet,
    
aar as [År],
    
datakilde
FROM
[QV filer til STAA\STAA_KOMMULERET.QVD]
(
qvd);

rupe
Valued Contributor

Re: Loading two data sets with identical variable names

LOAD

     datakilde = 'daglig',

     PERSON_ID,

.....

and in the same position after the Concatenate Load ....

LOAD

     datakilde = 'Aarlig',

     PERSON_ID,

....

This will then tag each set with either Aarlig or Daglig - so you can then put a listbar on the page for datakilde (with option always one selected item checked). That should work well ....

mariasdu
Contributor

Re: Loading two data sets with identical variable names

Can't I just load the "datakilde" variable as it is and then subset the data in the properties menu when I want to create a chart?

Just like if I have the variable gender and want to create a chart/table with only females (and not give the users the possibility to choose men)

Not applicable

Re: Re: Loading two data sets with identical variable names

Use the same script and you will get one table with combined data of both daily and yearly

Now what you want to do is , in your charting use this "datakilde" as flag for filtering for daily/yearly data .

Hope it helps you..

rupe
Valued Contributor

Re: Loading two data sets with identical variable names

I presume you are defining this in your set anaysis. Yes, you can do exactly that. I just used the above as an example with a listbar.

Obviously the potential advantage of a listbar is that you can then use the same objects for bot Aarlig or Daglig, but this may not be a relevant scenario for your analyses.

mariasdu
Contributor

Re: Loading two data sets with identical variable names

A set analysis was the keyword I was looking for. Thank you so much. Now I know what to search for.

rupe
Valued Contributor

Re: Loading two data sets with identical variable names

Something like:

Sum(${<datakilde = {Aarlig} >} Sales)

Community Browser