Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

paulyeo11
Valued Contributor II

How to combine 3 similar set of script into 1 ?

Hi All

I have 3 set of completely similar script , only different is the Excel Sheet Name . May i know how to convert 3 of them into 1 for easy maintenance ?

PRICE:

LOAD

     BRAND_P,

     Model as PART_NO_,

     Description,

     [PRODUCT_LINE] as [PRODUCT_TYPE],

     [PRODUCT_LINE],

     Supplier,

     [Product Group],

     [LOCAL PRODUCT],

     Currency_P,

     date([Updated]) as Updated,

     Price as LIST,

     Price as LIST_PRICE

    

         

FROM

[$(vRAWPath)$(vFILE1)]

(biff, embedded labels, table is SHEET1$);

LOAD

     BRAND_P,

     Model as PART_NO_,

     Description,

     [PRODUCT_LINE] as [PRODUCT_TYPE],

     [PRODUCT_LINE],

     Supplier,

     [Product Group],

     [LOCAL PRODUCT],

     Currency_P,

     date([Updated]) as Updated,

     Price as LIST,

     Price as LIST_PRICE

    

         

FROM

[$(vRAWPath)$(vFILE1)]

(biff, embedded labels, table is SHEET2$);

LOAD

     BRAND_P,

     Model as PART_NO_,

     Description,

     [PRODUCT_LINE] as [PRODUCT_TYPE],

     [PRODUCT_LINE],

     Supplier,

     [Product Group],

     [LOCAL PRODUCT],

     Currency_P,

     date([Updated]) as Updated,

     Price as LIST,

     Price as LIST_PRICE

    

         

FROM

[$(vRAWPath)$(vFILE1)]

(biff, embedded labels, table is SHEET3$);

1 Solution

Accepted Solutions
paulyeo11
Valued Contributor II

Re: How to combine 3 similar set of script into 1 ?

Hi Sok

Thank you now working fine.

Not sure why after i add lET vSheet=1; it work fine. below is the script :-

PRICE:

lET vSheet=1;

FOR Each vSheet in 'SHEET1$','SHEET2$','SHEET3$'

  LOAD

      BRAND_P,

      Model as PART_NO_,

      Description,

      [PRODUCT_LINE] as [PRODUCT_TYPE],

      [PRODUCT_LINE],

      Supplier,

      [Product Group],

      [LOCAL PRODUCT],

      Currency_P,

      date([Updated]) as Updated,

    Price as LIST,

      Price as LIST_PRICE

  FROM

  [$(vRAWPath)$(vFILE1)]

  (biff, embedded labels, table is $(vSheet));

Next vSheet;

7 Replies
paulyeo11
Valued Contributor II

Re: How to combine 3 similar set of script into 1 ?

Enclosed my Raw Data file from excel.

paulyeo11
Valued Contributor II

Re: Re: How to combine 3 similar set of script into 1 ?

Finally my QV doc.

Sokkorn
Honored Contributor

Re: How to combine 3 similar set of script into 1 ?

Hi Paul,

You may try this:

FOR Each vSheet in 'SHEET1$','SHEET2$','SHEET3$'

  LOAD

      BRAND_P,

      Model as PART_NO_,

      Description,

      [PRODUCT_LINE] as [PRODUCT_TYPE],

      [PRODUCT_LINE],

      Supplier,

      [Product Group],

      [LOCAL PRODUCT],

      Currency_P,

      date([Updated]) as Updated,

    Price as LIST,

      Price as LIST_PRICE

  FROM

  [$(vRAWPath)$(vFILE1)]

  (biff, embedded labels, table is $(vSheet));

Next vSheet;

Regards,

Sokkorn

paulyeo11
Valued Contributor II

Re: How to combine 3 similar set of script into 1 ?

Hi Sok

i try it not working. Must have miss comma some where.

error from.png

paulyeo11
Valued Contributor II

Re: How to combine 3 similar set of script into 1 ?

Ho Sok

vSheet how should i declare ?

Paul

Sokkorn
Honored Contributor

Re: How to combine 3 similar set of script into 1 ?

Hi Paul,

No need to declare vSheet. See my attached image

Untitled.png

Regards,

Sokkorn

paulyeo11
Valued Contributor II

Re: How to combine 3 similar set of script into 1 ?

Hi Sok

Thank you now working fine.

Not sure why after i add lET vSheet=1; it work fine. below is the script :-

PRICE:

lET vSheet=1;

FOR Each vSheet in 'SHEET1$','SHEET2$','SHEET3$'

  LOAD

      BRAND_P,

      Model as PART_NO_,

      Description,

      [PRODUCT_LINE] as [PRODUCT_TYPE],

      [PRODUCT_LINE],

      Supplier,

      [Product Group],

      [LOCAL PRODUCT],

      Currency_P,

      date([Updated]) as Updated,

    Price as LIST,

      Price as LIST_PRICE

  FROM

  [$(vRAWPath)$(vFILE1)]

  (biff, embedded labels, table is $(vSheet));

Next vSheet;

Community Browser