Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
New Contributor III

how to join same table with out synthetic keys

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





9 Replies
Highlighted

Re: how to join same table with out synthetic keys

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);


Highlighted

Re: how to join same table with out synthetic keys

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

Highlighted
New Contributor III

Re: how to  join same table  with out synthetic keys

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

Highlighted

Re: how to join same table with out synthetic keys

try this

LOAD

...

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

if (deptid<>101,EmpId) AS Emp

....

FROM $(vQVDBasePath)empQVD (QVD);

Highlighted
MVP & Luminary
MVP & Luminary

Re: how to join same table with out synthetic keys

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

Highlighted

Re: how to join same table with out synthetic keys

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

Highlighted
New Contributor III

Re: how to join same table with out synthetic keys

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

Highlighted

Re: how to join same table with out synthetic keys

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

Highlighted
New Contributor III

Re: how to  join same table  with out synthetic keys

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 :

Capture.PNG


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

Capture3.PNG

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