topic Re: Formatting Data File Layouts with QV? in QlikView

Formatting Data File Layouts with QV?

Anonymous — Mon, 23 Apr 2018 22:48:44 GMT

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

Re: Formatting Data File Layouts with QV?

Digvijay_Singh — Tue, 24 Apr 2018 00:16:46 GMT

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;

Re: Formatting Data File Layouts with QV?

Anonymous — Tue, 24 Apr 2018 00:52:37 GMT

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!

Re: Formatting Data File Layouts with QV?

Digvijay_Singh — Tue, 24 Apr 2018 02:32:49 GMT

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.

Re: Formatting Data File Layouts with QV?

Anonymous — Tue, 24 Apr 2018 02:58:53 GMT

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