topic Re: how to join same table with out synthetic keys in QlikView

how to join same table with out synthetic keys

Anonymous — Mon, 02 May 2016 07:25:05 GMT

Hi Team,

I have emp table FIRST TIME i am loading all the data in what i have QVD files.

second time i want to load the data with using conditional.

now i want to give the link /apply map with out synthetic keys.

Issue 1:

ex:

FIRST TIME loading:

load

empid,

name,

salary,

Bonus

country,

state,

city,

place,

deptid,

deptname

from $(vQVDBasePath)empQVD (QVD);

second time loading:

load

empid as 101empid,

country,

state,

city,

from $(vQVDBasePath)empQVD (QVD)

where deptid='101' and Bonous<>0

Note:

second time loading only for deptid='101'

now i want to merge the two tables in one single table

like :

empid,--here i need all empid"s

101empid,--here i need only deptid='101' and Bonous<>0 empid"s

name,

salary,

country,

state,

city,

place,

deptid,

deptname.

please help me

Regards,

Boby

Re: how to join same table with out synthetic keys

Kushal_Chawda — Mon, 02 May 2016 07:39:05 GMT

Try something like below

Data:

load *,

'101EmpId' as Flag

from $(vQVDBasePath)empQVD (QVD)

where deptid='101' and Bonous<>0;

concatenate(Data)

load *,

'Not101EmpId' as Flag

from $(vQVDBasePath)empQVD (QVD)

Where not exists(EmpID);

Re: how to join same table with out synthetic keys

Peter_Cammaert — Mon, 02 May 2016 07:47:52 GMT

Can be done in a simple way and all in one stroke. Like this:

Emp:

LOAD *,

if ((deptid = 101) and (bonous <> 0), empid) AS 101empid

FROM $(vQVDBasePath)empQVD (QVD);

or using Kush's technique, which may offer more flexilbility (more QlikView-ness) in the user interface:

Emp:

LOAD *,

if ((deptid = 101) and (bonous <> 0), '101EmpId', 'Not101EmpId') AS Flag

FROM $(vQVDBasePath)empQVD (QVD);

Best,

Peter

Re: how to join same table with out synthetic keys

Anonymous — Mon, 02 May 2016 10:52:17 GMT

hay kushal chawda,Peter Cammaert

i am confuzed

can you please give more explanation or please attach any QVD file

if ((deptid = 101) and (bonous <> 0), '101EmpId', 'Not101EmpId') AS Flag

here we are creating flag using this flag what we can do?

for ex:

we need emp and 101empid in one pivot is it possible?

Regards,

boby

Re: how to join same table with out synthetic keys

Kushal_Chawda — Mon, 02 May 2016 10:56:47 GMT

try this

LOAD

...

if ( deptid = 101 and bonous <> 0, EmpId) AS 101EmpID,

if (deptid<>101,EmpId) AS Emp

....

FROM $(vQVDBasePath)empQVD (QVD);

Re: how to join same table with out synthetic keys

marcus_sommer — Mon, 02 May 2016 10:58:54 GMT

The idea behind the suggestions from kushal chawda,Peter Cammaert is not to load your data twice else to flag the data to be able to differ them quite simply per selections or maybe with conditions within the expressions.

- Marcus

Re: how to join same table with out synthetic keys

Peter_Cammaert — Mon, 02 May 2016 11:02:35 GMT

There are three cases that you can deploy in a single pivot table using the Flag field QVD:

mix all employees: do not use the flag
aggregate just the facts for dept=101 bonus employees: add set analysis to your expression that selects Flag = {'101EmpId'}
aggregate the facts for non dept=101 employees: add set analysis to your expression that selects Flag = {'Not101EmpId'}

In you pivot table, you can put the three expressions next to each other.

Peter

Re: how to join same table with out synthetic keys

Anonymous — Mon, 02 May 2016 12:16:22 GMT

Hi Peter Cammaert ,

thanks for explaining

i am attaching QVD file Could you please reply on that your suggestions .

Fields names may be different but i need exact what i am asking in above

Regards,

Bby

Re: how to join same table with out synthetic keys

Peter_Cammaert — Mon, 02 May 2016 12:44:59 GMT

I don't really understand your (new) question. In your original post you asked for a second field that contains employee IDs only when they meet specific conditions. This field should be present in a single table and no synthetic keys should be present. Is it that what you want? My first example Re: how to join same table with out synthetic keys does exactly that.

If you want us to present you with an example of this script code, please post your source QVD (QlikView Data file), not your end QVW (QlikView document) because we cannot reload without the source QVD.

Peter

Re: how to join same table with out synthetic keys

Anonymous — Mon, 02 May 2016 16:18:20 GMT

let me give example for get above all things

here i have country,state,city and Date dimensions and "progerss, progerssamount ,estimateamt " metrics

here when i am creating a pivot using above fields

each metric level i want to restrict the null data in each date wise

like here ex:

in pivot we have progerss, and country,state,city with Date

so when i am creating to pivot i want to display each date wise values

like :

like above screens shot we can we can get but here i am marking the some dates so that dates i dont want to display in above pivot

note:

i dont want use any suppress null options or missing or hiding null options in pivot level so i must do some thing in Date column''

like here i need to create a alias date column with condition like

if(progerss<>0,'date') as aliasdate---but here i dont want to display else part means if the condition is true then it wil display date other wise it display "-" or ' '<spaces> in povit

so i dont want to else condition here

bellow screen shot i want to create as output :

here i dont want to use any suppress null or hiding null options in dimensional or metric level in pivot

observing the above two scree shots:

19,24,25 dates we dont have any values so i am restring that dates in Alias date column

my attention is:

in variable level or script level i want to restrict the 19,24,25 dates because these dates does contain any vale in progress metric.

and if i am using if condition in script level i am getting else part ..so i dont want that i want only

if particular date have some value then i need that date other wise i dont want date and '_' or ' '<spaces> like this i dont want ..

idea in script level:

testtable:

load

country,

state,

city,

Date,

month,

year,

progerss, progerssamount ,estimateamt

from TestTable

aliasdatefinding:

load Date as aliasdate

from TestTable:

where progress<>0;

so now how to merge or link these tables...because i need date columns ,aliasdate column.here Date column i need to use in some other metrics like progerssamount ,estimateamt

-----------------------------------after all above----simple words--

i need a new columns the column is derived by Date column with condition

like:condition is

progress have any value in particular date so i need only that date,,,that column name is "aliasdate"

this "alias date" cloumns--if possible i want to create in varible level or may be in script level also ...please see my QVD file above and please reply on that

regards,

bob