Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
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
try this
LOAD
...
if ( deptid = 101 and bonous <> 0, EmpId) AS 101EmpID,
if (deptid<>101,EmpId) AS Emp
....
FROM $(vQVDBasePath)empQVD (QVD);
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
There are three cases that you can deploy in a single pivot table using the Flag field QVD:
In you pivot table, you can put the three expressions next to each other.
Peter
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
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
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