topic Re: How to separate one field value in two field values with correct relation? in QlikView

How to separate one field value in two field values with correct relation?

Anonymous — Tue, 02 Feb 2016 15:26:04 GMT

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

Re: How to separate one field value in two field values with correct relation?

mphekin12 — Tue, 02 Feb 2016 17:15:13 GMT

how about this:

left(JobNr, 12) as MainJobNr,

if(len(JobNr)=14, JobNr) as SubJobNr

Re: How to separate one field value in two field values with correct relation?

Tue, 02 Feb 2016 17:37:03 GMT

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

Re: How to separate one field value in two field values with correct relation?

Anonymous — Wed, 03 Feb 2016 10:35:36 GMT

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

Re: How to separate one field value in two field values with correct relation?

mphekin12 — Wed, 03 Feb 2016 18:42:22 GMT

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?

Re: How to separate one field value in two field values with correct relation?

mgavidia — Wed, 03 Feb 2016 19:21:32 GMT

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;

Re: How to separate one field value in two field values with correct relation?

Wed, 03 Feb 2016 21:20:18 GMT

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!

Re: How to separate one field value in two field values with correct relation?

Anonymous — Thu, 04 Feb 2016 13:08:45 GMT

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

Re: How to separate one field value in two field values with correct relation?

vinieme12 — Sun, 10 Jul 2016 14:41:41 GMT

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