Formatting Data File Layouts with QV?

[Anonymous]

Hello,

I am attempting to achieve a specified data format for a bill of material conversion file with many many bills. I thought that QV would be an excellent tool to get the load file formatted correctly since there are a lot of BOMs. Essentially, I am looking to take a file with the header and details in one place and break it out with a new column: H/D - 'H' for header and 'D' for detail. The data will appear in a "one off" style as shown in the "Desired Format" tab. I have attached an example excel sheet with what we want to achieve:

1. Use the data found in the "Data to be used" tab to load into QV

2. Use scripting to get the file in the format found in the "Desired Format" tab

Please let me know if anything is unclear and your help is greatly appreciated. I tried:

1. loading table with distinct "header" details and concatenating line items with IF() logic to null values out where needed

2. Prev() function

[Digvijay_Singh · Accepted solution]

Try something like this  may be -

Input:

LOAD BOM_Material,

    ItemCat,

    Component,

    Qty,

    UoM

FROM

[..\..\..\..\..\..\Downloads\TEST_DATA.xlsx]

(ooxml, embedded labels, table is [Data to be used]);

Concatenate(Input)

Load

distinct

BOM_Material,

'H' as "H/D"

Resident Input;

NoConcatenate

Final:

Load

RecNo() as ID,

if(len("H/D")=0,'D',"H/D") as "H/D",

if(BOM_Material=Previous(BOM_Material),'',BOM_Material) as BOM_Material,

if(len(ItemCat)=0,'',ItemCat) as ItemCat,

if(len(Component)=0,'',Component) as Component,

if(len(Qty)=0,'',Qty) as Qty,

if(len(UoM)=0,'',UoM) as UoM

Resident Input

Order By BOM_Material,"H/D" DESC;

Drop table Input;

[Anonymous]

Sir,

This is exactly what we were trying to do. Our code looked similar up until the final load in your script. Thanks a ton. If I may ask one more question: the chart that you used is a straight table, is there any particular thing I need to look out for if we use a tablebox instead?

Thanks again!

[Digvijay_Singh]

Glad it worked for you.

I think sorting in the load order may be challenging but below small change in bold made things easy.

Load

RecNo() as ID,

Dual(if(len("H/D")=0,'D',"H/D"),RecNo()) as "H/D",

if(BOM_Material=Previous(BOM_Material),'',BOM_Material) as BOM_Material,

if(len(ItemCat)=0,'',ItemCat) as ItemCat,

if(len(Component)=0,'',Component) as Component,

if(len(Qty)=0,'',Qty) as Qty,

if(len(UoM)=0,'',UoM) as UoM

Resident Input

Order By BOM_Material,"H/D" DESC;

This is table box.

[Anonymous]

Thanks so much again! This is an interesting use of Dual() to me.