Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rpayn01215
Contributor

Formatting Data File Layouts with QV?

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

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Formatting Data File Layouts with QV?

Try something like this  may be -

Capture.PNG

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;

View solution in original post

4 Replies
Digvijay_Singh
Honored Contributor III

Re: Formatting Data File Layouts with QV?

Try something like this  may be -

Capture.PNG

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;

View solution in original post

rpayn01215
Contributor

Re: Formatting Data File Layouts with QV?

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
Honored Contributor III

Re: Formatting Data File Layouts with QV?

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.

Capture.PNG

rpayn01215
Contributor

Re: Formatting Data File Layouts with QV?

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