Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi together,
could someone give me a tip how I can solve my problem?
I have a field which contains the Main Job Number and the Sub Job Number.
Now I want to separate both Numbers in two different fields.
When I do that by this way I loose the relation to the Main Job Number:
if(len(JobNr)=12, JobNr) as MainJobNr,
if(len(JobNr)=14, JobNr) as SubJobNr
What I can do to let QlikView know that a Sub Job Number belong to a Main Job Number?
In any case: Sub Job Number= Main Job Number + -A, -B, -C, ...
Best regards
Lisa
how about this:
left(JobNr, 12) as MainJobNr,
if(len(JobNr)=14, JobNr) as SubJobNr
I agree with mphekin12 on the load syntax. I will caution that from what I see with your example, this will produce a row where SubJobNr is NULL. Given what I see from your expectations on the pivot, a null value for SubJobNr is not desired.
You might want to use
LOAD
left(JobNr, 12) as MainJobNr,
if(len(JobNr)=14, JobNr) as SubJobNr
Where LEN(JobNr) > 12
Hi mphekin12 and Mika,
thanks for the fast answer. I have try both suggestions and came to a further challenge.
(1= mphekin and 2= Mike)
I have Main Jobs without Subjobs and inverse ... that leads to wrong values.
I tried to explain it in a table ...
and this time I have prepared a file with the 3 cases.
Would please somebody look at it?
Lisa
If you go to the Presentation tab of the Tmp1 Pivot Table and unselect 'Suppress Zero-Values', all of the records will display. Is that what you are looking for?
Is it something like this what you are looking for?
(I added some amounts to the jobs to get a more descriptive screen shot)
If that is what you need, here is how to get it:
MainJobNr:
LOAD
JobNr AS MainJobNr
FROM
[..\..\..\..\..\..\..\Documents\Examples.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE LEN(JobNr)=12;
SubJobNr:
LOAD
LEFT(JobNr,12) AS MainJobNr,
JobNr AS SubJobNr,
Amount
FROM
[..\..\..\..\..\..\..\Documents\Examples.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE LEN(JobNr)=14;
So it's possible to have a SubJobNr without a MainJobNr? I guess I misunderstood the relationships. The script will always create a record for MainJobNr even if there isn't technically one in the data. Is that what you want?
Why would you need a JobNr list box if you have list boxes for MainJobNr and SubJobNr? Wouldn't you have all you records covered by those two list boxes? I commented it out here and you can see it in the app I attached. Also, if you do a count on SubJobNr in the pivot, you will not be able to see the records where where is a MainJobNr and no SubJobNr, so I changed it to count MainJobNr instead.
Test2:
LOAD //JobNr as JobNr2,
Left(JobNr, 12) as MainJobNr2,
if(len(JobNr)= 14, JobNr) as SubJobNr2
FROM
Examples.xls
(biff, embedded labels, table is Sheet1$)
where len(JobNr) > 12;
LOAD
//JobNr as JobNr2,
Left(JobNr, 12) as MainJobNr2
FROM
Examples.xls
(biff, embedded labels, table is Sheet1$)
where len(JobNr) = 12;
I hope this helps!
Hi together,
unfortunately it is not the solution I'm looking for. I'll try to explain it step by step.
But that what you have written helped me! Now I missing only two steps to the searched result. I have a bit combined :-).
At Mike: In theory belong a Sub-Job to a Main-Job. Sometimes define a project manager his jobs incorrectly.
Unfortunately, this is possible (create a Sub-Job without Main-Job). And not all Main-Jobs have a Sub-Job.
Becauce that I have tried to explain the problem with the 3 cases in my last post. Sorry, at the begin at this work I do not know it.
I need three fields, because users do not know always how the job is created. All 3 field should be possible for the surch
and selection. The seperation I will use as dimension in pivot tables.
To the values of the job: Each position has its own value.
Main Job: 7.000 €
Sub Job-A: 2.000 €
Sub Job-B: 4.000 €
Amount of a Job-Group= 13.000 €
// ---------------------------------------------------------------------
This ist my new status ->
All_Jobs:
LOAD
JobNr
FROM
Examples.xls
(biff, embedded labels, table is Sheet1$);
Left Join (All_Jobs)
// All MainJobs - Case 1 + just MainJobs of Case 2
LOAD
JobNr,
JobNr AS MainJobNr
//[Job Value]
FROM
Examples.xls
(biff, embedded labels, table is Sheet1$)
WHERE LEN(JobNr)=12;
NoConcatenate
Sub_Tmp: // All Subjobs
LOAD
JobNr,
JobNr AS SubJobNr
//[Job Value]
FROM
Examples.xls
(biff, embedded labels, table is Sheet1$)
WHERE LEN(JobNr)=14;
Left Join (All_Jobs)
// All Subjobs with MainJob - Case 2
LOAD
SubJobNr,
left(SubJobNr, 12) as MainJobNr
//[Job Value]
Resident Sub_Tmp;
Concatenate (All_Jobs)
//Left Join (All_Jobs)
Load // All Subjobs without MainJob (Case 3)
JobNr,
//'-' as MainJobNr2,
SubJobNr
//[Job Value]
Resident Sub_Tmp
Where not exists(MainJobNr, left(SubJobNr, 12));
Drop Table Sub_Tmp;
// ---------------------------------------------------------------------
Case 1 - MainJob without SubJob = work
Case 2 - MainJob with SubJobs = work partly
I missing one line. That is important,
That I need:
Goal
Now
Case 3 - SubJob without MainJob = work partly
Selection in SubJob work.
If select about JobNr = one line to much.
I need just the first line.
Do anyone know how I can solve the problem?
Lisa
Here is the script i used, let me know if it resolves your problem
TEMP:
LOAD
if(len(JobNr)>12 ,left(JobNr,12),JobNr) as JobNr,
if(len(JobNr)>12 ,JobNr) as SubJobNr,
[Job Value]
FROM
(biff, embedded labels, table is Sheet1$);
FACT:
LOAD
JobNr,
if(isnull(SubJobNr),JobNr,SubJobNr) as SubJobNr,
[Job Value] as Job_Value
RESIDENT TEMP;
DROP TABLES TEMP;
Cheers
V