Extracting a Single Multi-value from Another File

Hi Guys,

got a conundrum for you !!!

one of my users wanted a simple report to extract details for a part that needed data from the main report file containing most of the required data but also from another file in the same data account. BTW. We are Universe users

The details in the local file are all single values, however the field for data from the other file is multivalued and the user only needs the first multi-value from the second field in the record in the other file.

as this request was likely to be a one off, which I thought would a simple quick and dirty job, I did not want to bother with a sub-routine or create a program to explode the MV's in the other file into a workfile first or indeed write a program to do the whole report which needed to go into Excel.

So I naively assumed that extracting this detail would be easily doable with I Descriptors.

this is what the data record looks like in the second file

0001: 04551VM14550VM04913VM04912
0002: 15143VM15148VM15150VM15160
0003: 12VM12VM12VM12
0004: 1800000VM1848000VM1776000VM1764000

As you can see a simple 4 field record. NB: Some of these records have 100's of MV's

I want extract Attribute<2,1> 15143

In the host file for this data this works and correctly displays 15143

0001: I
0002: FIELD(CPART,@VM,1,1) [cpart is attribute 2]
0003:
0004: CUST PART NO
0005: 30L
0006: S

So in the dictionary of the file I want to report on this also works to fetch all 4 values from attribute <2>

0001: I
0002: TRANS(FILENAME,PARENT.PART.NBR,CPART,"X")
0003:
0004: CUST PART NO
0005: 30L
0006: S

BOM KEY... CUST PART NO.

216962 15143

15148

15150

15160

Naturally I thought that this would also work !!!!!

0001: I
0002: FIELD(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

WRONG it displays all the MV's

BOM KEY... CUST PART NO..
216962 15143

15148

15150

15160

I then changed it to FIELDS and it made no difference

0001: I
0002: FIELDS(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

BOM KEY... CUST PART NO.

216962 15143

15148

15150

15160

Now at this stage I realised that as FIELD and FIELDS was not going to work, I went searching in the UniVerse_BASICCommandsReference_V1134.pdf to explore for alternative commands that may work

so I tried CONVERT in changing the @VM to "*" and then wanted to apply a FIELD around that, the CONVERT I Description Complied,

0001: I
0002: FIELD(CONVERT(@VM,"*",(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"))),"*",1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

but still just returned

BOM KEY... CUST PART NO..

216962 15143

15148

15150

15160

Tried CHANGE that won't compile in an I descriptor

Tried REPLACE and did not get anything sensible

I have had some success with EXTRACT

0001: I
0002: EXTRACT(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

This does deliver the required output, put very annoyingly it displays on screen

Program ".ITYPE.": pc = 1E, Nonnumeric data when numeric required. Zero used.

For each row of output data, this is annoying, However I've hidden it from the screen with a HUSH, if this report paragraph ever sees the light of day again

Just trying to save others from going through the same hoops I've tried, in the end it would have been much quicker to write a quick Basic Program, but, sometimes, obstinacy takes over…..

I have got the User her data into a CSV file, but I would like to ask of you all, have you a neat simple solution for extracting a Single MV value from another file just using an I descriptor or 2, so it can be used in Retrieve paragraph ???

Thanks in advance

Andrew

------------------------------
Andrew Milne
Business Systems Manager
Potter and Moore Innovations
Peterborough, Cambs GB
------------------------------

A simple way would be to write a subroutine to do this and then create a SUBR I-Type

A simple subroutine such as (note the read statement assumes the key to FILE2 is stored in Attribute one of FILE1)

001: SUBROUTINE GETMV2.1(VAL)
002: VAL = ""
003: OPEN "FILE2" TO F.FILE2 ELSE RETURN
004: READ R.FILE2 FROM F.FILE2,@RECORD<1> ELSE R.FILE2 = ""
005: VAL = EXTRACT(R.FILE2,2,1,0)
006: RETURN
007: END

Then a SUBR I-Type in the dictionary of FILE1 as below

001: I
002: SUBR("GETMV2.1")
003:
004: MV2.1
005: 10L
006: S

------------------------------
Jonathan Smith
UniData ATS
Rocket Support
------------------------------

Hi Guys,

got a conundrum for you !!!

one of my users wanted a simple report to extract details for a part that needed data from the main report file containing most of the required data but also from another file in the same data account. BTW. We are Universe users

The details in the local file are all single values, however the field for data from the other file is multivalued and the user only needs the first multi-value from the second field in the record in the other file.

as this request was likely to be a one off, which I thought would a simple quick and dirty job, I did not want to bother with a sub-routine or create a program to explode the MV's in the other file into a workfile first or indeed write a program to do the whole report which needed to go into Excel.

So I naively assumed that extracting this detail would be easily doable with I Descriptors.

this is what the data record looks like in the second file

0001: 04551VM14550VM04913VM04912
0002: 15143VM15148VM15150VM15160
0003: 12VM12VM12VM12
0004: 1800000VM1848000VM1776000VM1764000

As you can see a simple 4 field record. NB: Some of these records have 100's of MV's

I want extract Attribute<2,1> 15143

In the host file for this data this works and correctly displays 15143

0001: I
0002: FIELD(CPART,@VM,1,1) [cpart is attribute 2]
0003:
0004: CUST PART NO
0005: 30L
0006: S

So in the dictionary of the file I want to report on this also works to fetch all 4 values from attribute <2>

0001: I
0002: TRANS(FILENAME,PARENT.PART.NBR,CPART,"X")
0003:
0004: CUST PART NO
0005: 30L
0006: S

BOM KEY... CUST PART NO.

216962 15143

15148

15150

15160

Naturally I thought that this would also work !!!!!

0001: I
0002: FIELD(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

WRONG it displays all the MV's

BOM KEY... CUST PART NO..
216962 15143

15148

15150

15160

I then changed it to FIELDS and it made no difference

0001: I
0002: FIELDS(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

BOM KEY... CUST PART NO.

216962 15143

15148

15150

15160

Now at this stage I realised that as FIELD and FIELDS was not going to work, I went searching in the UniVerse_BASICCommandsReference_V1134.pdf to explore for alternative commands that may work

so I tried CONVERT in changing the @VM to "*" and then wanted to apply a FIELD around that, the CONVERT I Description Complied,

0001: I
0002: FIELD(CONVERT(@VM,"*",(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"))),"*",1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

but still just returned

BOM KEY... CUST PART NO..

216962 15143

15148

15150

15160

Tried CHANGE that won't compile in an I descriptor

Tried REPLACE and did not get anything sensible

I have had some success with EXTRACT

0001: I
0002: EXTRACT(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

This does deliver the required output, put very annoyingly it displays on screen

Program ".ITYPE.": pc = 1E, Nonnumeric data when numeric required. Zero used.

For each row of output data, this is annoying, However I've hidden it from the screen with a HUSH, if this report paragraph ever sees the light of day again

Just trying to save others from going through the same hoops I've tried, in the end it would have been much quicker to write a quick Basic Program, but, sometimes, obstinacy takes over…..

I have got the User her data into a CSV file, but I would like to ask of you all, have you a neat simple solution for extracting a Single MV value from another file just using an I descriptor or 2, so it can be used in Retrieve paragraph ???

Thanks in advance

Andrew

------------------------------
Andrew Milne
Business Systems Manager
Potter and Moore Innovations
Peterborough, Cambs GB
------------------------------

Hi Andrew,
When the TRANS() function is used in a I-type descriptor and the data returned contains system delimiters, the value of those delimiters is lowered. So in your case, the value mark delimited string will be returned with the value marks returned replaced by subvalue marks. I believe a simple solution for your example would be to replace the @VM to @SVM in the I-descriptor expression.

0002: FIELD(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@SVM,1,1)

Another solution is to wrap the result of the TRANS() function with the RAISE() function. UniVerse has both RAISE() and LOWER() functions to either raise or lower a system delimiter value one level, respectively. I have seem them used particularly in I-descriptions when a TRANS() function is involved.

Thanks,

Neil

------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------

Thanks Guys.

Perfect solutions, not only have I got great answers i've also learned something very useful.

I've been using Prime Information since late 80's then Universe in the 2000's and have only just learned about the the Value marks being lowered.

This is what i like about this forum, the practical knowledge is always being shared, and hopefully someone else will now also get the benefit

Thanks again for you help

Have a great weekend

Andrew

------------------------------
Andrew Milne
Business Systems Manager
Potter and Moore Innovations
Peterborough, Cambs GB
------------------------------

Thanks Guys.

Perfect solutions, not only have I got great answers i've also learned something very useful.

I've been using Prime Information since late 80's then Universe in the 2000's and have only just learned about the the Value marks being lowered.

This is what i like about this forum, the practical knowledge is always being shared, and hopefully someone else will now also get the benefit

Thanks again for you help

Have a great weekend

Andrew

------------------------------
Andrew Milne
Business Systems Manager
Potter and Moore Innovations
Peterborough, Cambs GB
------------------------------

Or use RAISE.

------------------------------
Kate Stanton
Software Developer
Walstan Systems
AUCKLAND NZ
------------------------------

Hi Guys,

got a conundrum for you !!!

one of my users wanted a simple report to extract details for a part that needed data from the main report file containing most of the required data but also from another file in the same data account. BTW. We are Universe users

The details in the local file are all single values, however the field for data from the other file is multivalued and the user only needs the first multi-value from the second field in the record in the other file.

as this request was likely to be a one off, which I thought would a simple quick and dirty job, I did not want to bother with a sub-routine or create a program to explode the MV's in the other file into a workfile first or indeed write a program to do the whole report which needed to go into Excel.

So I naively assumed that extracting this detail would be easily doable with I Descriptors.

this is what the data record looks like in the second file

0001: 04551VM14550VM04913VM04912
0002: 15143VM15148VM15150VM15160
0003: 12VM12VM12VM12
0004: 1800000VM1848000VM1776000VM1764000

As you can see a simple 4 field record. NB: Some of these records have 100's of MV's

I want extract Attribute<2,1> 15143

In the host file for this data this works and correctly displays 15143

0001: I
0002: FIELD(CPART,@VM,1,1) [cpart is attribute 2]
0003:
0004: CUST PART NO
0005: 30L
0006: S

So in the dictionary of the file I want to report on this also works to fetch all 4 values from attribute <2>

0001: I
0002: TRANS(FILENAME,PARENT.PART.NBR,CPART,"X")
0003:
0004: CUST PART NO
0005: 30L
0006: S

BOM KEY... CUST PART NO.

216962 15143

15148

15150

15160

Naturally I thought that this would also work !!!!!

0001: I
0002: FIELD(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

WRONG it displays all the MV's

BOM KEY... CUST PART NO..
216962 15143

15148

15150

15160

I then changed it to FIELDS and it made no difference

0001: I
0002: FIELDS(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

BOM KEY... CUST PART NO.

216962 15143

15148

15150

15160

Now at this stage I realised that as FIELD and FIELDS was not going to work, I went searching in the UniVerse_BASICCommandsReference_V1134.pdf to explore for alternative commands that may work

so I tried CONVERT in changing the @VM to "*" and then wanted to apply a FIELD around that, the CONVERT I Description Complied,

0001: I
0002: FIELD(CONVERT(@VM,"*",(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"))),"*",1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

but still just returned

BOM KEY... CUST PART NO..

216962 15143

15148

15150

15160

Tried CHANGE that won't compile in an I descriptor

Tried REPLACE and did not get anything sensible

I have had some success with EXTRACT

0001: I
0002: EXTRACT(TRANS(FILENAME,PARENT.PART.NBR,CPART,"X"),@VM,1,1)
0003:
0004: CUST PART NO
0005: 30L
0006: S

This does deliver the required output, put very annoyingly it displays on screen

Program ".ITYPE.": pc = 1E, Nonnumeric data when numeric required. Zero used.

For each row of output data, this is annoying, However I've hidden it from the screen with a HUSH, if this report paragraph ever sees the light of day again

Just trying to save others from going through the same hoops I've tried, in the end it would have been much quicker to write a quick Basic Program, but, sometimes, obstinacy takes over…..

I have got the User her data into a CSV file, but I would like to ask of you all, have you a neat simple solution for extracting a Single MV value from another file just using an I descriptor or 2, so it can be used in Retrieve paragraph ???

Thanks in advance

Andrew

------------------------------
Andrew Milne
Business Systems Manager
Potter and Moore Innovations
Peterborough, Cambs GB
------------------------------

Does OCONV with a T)ranslate option not work in Universe? I'm guessing from all the responses with alternatives that this doesn't work in UV?

In Unidata this I-type descriptor would return the first MV of attribute 2.

OCONV(PARENT.PART.NBR, "TFILENAME;X1;2;2")

Where the X1 tells the TRANSLATE to take the first MV field of attribute 2. I use EXTRACT to get an arbitrary MV (usually the last one), but really common to want to get the first...

------------------------------
Ian McGowan
Principal Consultant
Rocket Forum Shared Account
------------------------------

Hi Ian,
The 'Tfilename' syntax you note will work in UniVerse. The previous response to use @SVM or the RAISE() function was simply to get the original choice of syntax to work. I guess another example of having multiple ways to get to the same end result. The "Tfilename" syntax you noted does look like a good method for extracting individual values from a translated field.
Thanks,
Neil

------------------------------
Neil Morris
Universe Advanced Technical Support
Rocket Software
------------------------------

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded