Use String as Field in Diagram Function

[LeHaoNguyen]

Hi all,

i want to make a diagram to show the working stock of each process point.

So i got two tables.

One with all Timestamps from CheckIn to CheckOut with the following Columns:

Projectnumber, Documentbarcode, CheckInDate, PreparationDate, ClassificationDate, CheckOutDate

if a Datefield isnt filled it didnt went through this processstep yet and is NULL.

And one Table with the Process Order with following Columns:

Projectnumber, Processnumber, Processname

Because every Project can have different Process Orders we need the 2nd Table to see the right order.

My Goal is now if the user filter on a Projectnumber to show how many Documentbarcodes are already gone through processnumber 1 but not 2,3... and how many are gone through processnumber 2 but not 3,4... and so on.

My Idea was to create a new Field in the 2nd Table: tmp_field.  The Fieldvalue of this field contains the name of the Column it need to go to. So if  Processname of the Row is Checkin in the new Field would be CheckInDate. Now i want to parse the string i get somehow

=Only({$<Processnumber={1}>}tmp_field)

or

=FieldValue('tmpfield','1')

both return CheckInDate. But i need the List of Values from the first Table. is there a way to return all Entries of the Column CheckInDate from the first Table somehow?

[gmenoutis]

Can you tell us your source data structure? It seems the first table is a pivoted version of the second which is how your source data are structured.

[LeHaoNguyen]

So this is my first Table i only show 4 dates but we got much more like 11:

Projectnumber	Documentbarcode	CheckInDate	PreparationDate	ClassificationDate	CheckOutDate
123456	123456A000001	11.02.2019	15.02.2019	13.02.2019	19.02.2019
123456	123456A000002	11.02.2019	NULL	14.02.2019	NULL

the 2nd table:

Projectnumber	Processnumber	Processname	tmp_field
123456	1	CheckIn	CheckInDate
123456	2	Classification	ClassificationDate
123456	3	Preparation	PreparationDate
123456	4	CheckOut	CheckOutDate

i hope now its clear for you what i want

[marcus_sommer]

A string couldn't be used as fieldname and I also think that any approach with fieldvalue() won't be working respectively be practically.

I think I would just use a mapping-approach maybe something like this:

m: mapping load
      Projectnumber, CheckInDate & '|' & ClassificationDate & '|' & PreparationDate & '|' &   CheckOutDate
      from table1;

t:   load *, subfield(applyamp('m',Projectnumber, null()), '|', Processnumber) as New
      from table2;

- Marcus

[LeHaoNguyen]

Hi Marcus,

i think your soulution wont help me at my problem let me describe it a bit further we got two initial tables with one with dates of each processstep:

Projectnumber	Documentbarcode	CheckInDate	PreparationDate	ClassificationDate	CheckOutDate
123456	123456D000001	11.02.2019	15.02.2019	16.02.2019	23.02.2019
123456	123456D000002	15.02.2019	NULL	NULL	NULL
123456	123456D000003	14.02.2019	16.02.2019	NULL	NULL
123456	123456D000004	17.02.2019	NULL	NULL	NULL
654321	654321D000001	11.02.2019	14.02.2019	13.02.2019	NULL
654321	654321D000002	13.02.2019	NULL	15.02.2019	NULL
654321	654321D000003	14.02.2019	NULL	19.02.2019	NULL

and one with processorder for each project:

Projectnumber	Processnumber	Processstep
123456	1	CheckIn
123456	2	Preparation
123456	3	Classification
123456	4	CheckOut
654321	1	CheckIn
654321	2	Classification
654321	3	Preparation
654321	4	CheckOut

So in project 123456 we got following process order:CheckIn>Preparation>Classification>CheckOut

and in Project 654321 we got following process Order: CheckIn>Classification>Preparation>CheckOut

now i want to create a third table which has the workstock for each processstep grouped by Projectnumber:

Projectnumber	PreparationWorkstock	ClassificationWorkstock	CheckOutWorkStock
123456	2	1	0
654321	2	0	1

So the Workstock of each processstep is determined by the order of the processsteps but i dont know how to link those two tables together to get the desired table.

So the Logic is if field is filled then check what is the next projectstep if its empty and all following till the end are empty the count of the workstep after the filled one get +1.

I hope you understand what the goal is. it is not easy to explain 🙂

[gmenoutis]

What you hope to achieve is VERY complex, and certainly not possible with one line of code.

Do you have the ability to make changes to the script?

[marcus_sommer]

Instead of using the Processnumber you could apply a numeric value for each Processstep maybe in beforehand with a dual() or just within the subfield(), for example:

subfield(applyamp('m',Projectnumber, null()), '|',
   match(Processstep, 'CheckIn', 'Classification', 'Preparation', 'CheckOut'))

- Marcus

[gmenoutis]

Also: Can there be missing dates? For example, could a projectnnumber 654321 with barcode 654321D000004 have a checkin date (step 1), a prepeartaion date (step 3), but be missing the value for classification date (step 2)? If yes, then how would this barcode be counted in that case?

[LeHaoNguyen]

yes this can happen but is not really common then its in the workstock for checkout(step 4)

and i can make any changes to the script(data structure if needed)

[gmenoutis · Accepted solution]

Unfortunately, it seems that when using the evaluation operator, it gets executed at table level and not at row level, so we cant have a custom formula for each row.

Fortunately though, it can be done with some table modifications. Run the following script, and replace A with the name of your first table (the one with the date values) and B with your second table (the one with Processnumber). In the end, you will have a new column in the first called "NextProcessStep" which shows...what it says. Now the set analysis for your metrics is a simple sum.

// This creates a crosstable (unpivoted) table: For each Documentbarcode there are as many rows as non-null dates
C: Crosstable (Processstep,ProcessDate,2)
LOAD
Projectnumber,Documentbarcode // qualifiers
,CheckInDate as CheckIn
,PreparationDate as Preparation
,ClassificationDate as Classification
,CheckOutDate as CheckOut
resident A;

// Depending on the Projectnumber let's get the next processstep for each line
left join(C)
LOAD Projectnumber,Processnumber,Processstep,if(previous(Projectnumber)=Projectnumber,previous(Processstep),null()) as NextProcessstep
resident B
order by Projectnumber,Processnumber desc;

// We return the info to the first table by joining. We only keep the latest filled date record, and we have the next step.
left join(A)
LOAD Projectnumber,Documentbarcode, firstsortedvalue(NextProcessstep,-Processnumber) as NextProcessstep
resident C
group by Projectnumber,Documentbarcode;

drop table C;