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

    add new column in Qlikview converter

    Trapti Gaharwar



      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')






      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;





        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




        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

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



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



        ,[SO Name]

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

        ,COC as Cnty



        ,[City Name]


        ,[Sold-To Code]

        ,[Sold-To Name]

        ,[Ship-To Code]

        ,[Ship-To Name]

      // ,Delcreder // Not used

        ,[Sh SGrp] // Sgr= District officer


        ,[Cust Grp]


        ,[Plant Name]


        ,[CMU Desc.]

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

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

        ,[Material Code]

        ,[Material Desc]


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


      // ,ShC // always "0"


        ,MODE as Trnsp




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

      // ,Inv.date as x_Inv_Date


        ,[Gross Price]

      // ,[Cash Disc.]

      // ,[QDInv Rs/%]

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


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

      // ,Octroi // always 0

      // ,BarrTax // always 0

      // ,AGT // always 0

      // ,EntryTax // always 0

        ,[Net Bill Price]





        ,[Basic Exc]

        ,[Edu Cess]


        ,[Base Price]

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

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

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





        ,[SecFrt Z1AF]


      // ,OctAcr // always 0

      // ,BarTaxAcr // always 0




        // ,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;