1 Reply Latest reply: Jul 21, 2017 2:30 AM by Gysbert Wassenaar RSS

    add new column in Qlikview converter

    Trapti Gaharwar

      Hi,

       

      I want to add a new field that is '[Can Inv ind]' in my qlikview converter.

      While doing this i am getting field not found error.

       

      Kindly help me to resolve this problem.

      Pls find below the script

       

      //==========================================================================================

      // 1. Convert TXT2QVD

      //==========================================================================================

      /*******************************************************************************************/

       

       

      For each TextFile in filelist ('ZSD_0082_*.txt')

       

       

      DUMP:

      load

        *

      from $(TextFile) (ansi, txt, delimiter is '|', embedded labels, header is 68 lines, no quotes, filters(

      Remove(Col, Pos(Top, 1))

      ))

      //where isnum([Inv. no.]);

      where isnum([Reff.Doc No]);

       

       

      LET x = TextFile;

      Let y = mid(x,index(x,'\',-1)+1,len(x)-4-index(x,'\',-1));

       

       

      store DUMP into _dump_$(y).qvd;

       

       

      DSR:

      load

        rowno() as id_dsr

        ,year(date#(Inv.date,'DD.MM.YYYY')) as Year

        ,num(year(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'))&mid(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'),4,2)) as month

        ,month(date#(Inv.date,'DD.MM.YYYY')) as Month

        ,weekyear(date#(Inv.date,'DD.MM.YYYY'))&

        if(len(week(date#(Inv.date,'DD.MM.YYYY')))<2,

        0&week(date#(Inv.date,'DD.MM.YYYY')),

        week(date#(Inv.date,'DD.MM.YYYY'))) as Week

        ,left(CMU,2) as Plant

        ,left([SO Name],3) as SU

        ,if(right([Cash Disc.],1) = '-',evaluate(1.00*left([Cash Disc.],len([Cash Disc.])-1)),1.00*[Cash Disc.]) as [Cash Disc.]

        ,if(right([QDInv Rs/%],1) = '-',evaluate(1.00*left([QDInv Rs/%],len([QDInv Rs/%])-1)),1.00*[QDInv Rs/%]) as [QDInv Rs/%]

        ,date#(Inv.date,'DD.MM.YYYY') as Inv.date

        , num(year(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'))&mid(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'),4,2))&COC as key_allocation

        , num(year(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'))&mid(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'),4,2))&[Sold-To Code] as key_lakshya

        , [Sold-To Code]&Plnt&[Material Code] as key_pending_orders

      // =====================================================================================================================================================================

        ,DCh

        ,Cust.g5

      // ,CG2 // GMU better take the first three letters of the area offices, these sales unit are linked to the customer

        ,Cust.g4

        ,Rg

        ,[SO Name]

        //,[Sales Dist.] //Warehouse as linked to the customer

        ,COC as Cnty

        ,County

        ,City

        ,[City Name]

        ,CG1

        ,[Sold-To Code]

        ,[Sold-To Name]

        ,[Ship-To Code]

        ,[Ship-To Name]

      // ,Delcreder // Not used

        ,[Sh SGrp] // Sgr= District officer

        ,Cust.g3

        ,[Cust Grp]

        ,Plnt

        ,[Plant Name]

        ,CMU

        ,[CMU Desc.]

        //, right([CMU Desc.],2) as Rail_or_Road

      // ,MG2 as prod_grp // not to be used

        ,[Material Code]

        ,[Material Desc]

        //,InTy

        , [Reff.Doc No] as [Inv. no.]

        ,Contract

      // ,ShC // always "0"

        ,Inco

        ,MODE as Trnsp

        ,Route

        ,Dist

        ,Description

      // ,Tx as x_Tx // Tax related, to be ignored

      // ,Inv.date as x_Inv_Date

        ,QTY

        ,[Gross Price]

      // ,[Cash Disc.]

      // ,[QDInv Rs/%]

      // ,[TDInv Rs/%.] // always 0

        ,Sec.frt

      // ,[C&FInv] // always 0

      // ,Octroi // always 0

      // ,BarrTax // always 0

      // ,AGT // always 0

      // ,EntryTax // always 0

        ,[Net Bill Price]

        ,Tax

        ,ExcPMT

        ,Exc%

        ,MRP

        ,[Basic Exc]

        ,[Edu Cess]

        ,[Sec&HECess]

        ,[Base Price]

      // ,[QDAcrRs/%] // always 0

      // ,[TDAcrRs/%] // always 0

      // ,[ADAcrRs/%] // always 0

        ,PrivDisc

        ,OPAcom

        ,CACcom

        ,SPAcom

        ,[SecFrt Z1AF]

        ,[C&Facr]

      // ,OctAcr // always 0

      // ,BarTaxAcr // always 0

        ,AGTAcr

        ,UnincFrt

        ,NDPJ08Acr

        // ,Survey.chrgs // always 0

        // ,[Port Chrgs] // always 0

        ,[Can Inv ind]

      resident DUMP;

       

       

       

       

      store DSR into dsr_$(y).qvd;

       

       

      drop tables DUMP, DSR;

       

       

      Next TextFile;