1 Reply Latest reply: Oct 6, 2015 9:21 AM by RSS

    sql dynamic load data and union multiple tables

    Stanislav Chernov

      hello,

      1. i'll have sql query, and it work perfect, how i can convert it to QlikView-script ?

       

       

      use SES

      declare @operday int

      set @operday='20130613'

       

      select * from ChequeHead,ChequePos,ChequeDisc where ChequeHead.OperDay = @operday and ChequeHead.Id=ChequePos.ChequeId and chequepos.Id = chequedisc.PosId 

      select * from ChequeHead,ChequePos,ChequeDisc where ChequeHead.OperDay = @operday and ChequeHead.Id=ChequePos.ChequeId and chequepos.Id = chequedisc.PosId 

      select * from ChequeHead,ChequePos,ChequeDisc where ChequeHead.OperDay = @operday and ChequeHead.Id=ChequePos.ChequeId and chequepos.Id = chequedisc.PosId 

       

       

       

       

      OR, i vave qv-script, and i want to select data from 4 tables, i have linked the tables by ID
      I have one value OperDay, using it in the same table, I want to load only through SQL related rows in other tables of the first request, how i can do it?

       

      and how you can update the data in the tables, if for example I will upload data each day, only changing the value of OperDay, or you can do this automatically using the Today (), converting it to Num ()

       

      OLEDB CONNECT TO [Provider=SQLOLEDB.1;........

       

      LOAD Id as [ChequeHeadID],

          Taken,

          Operation,

          ShopIndex,

          ShopIndexKKM,

          OperDay,

          "Cash_Code",

          NSmena,

          DateOperation,

          "Ck_Number",

          ControlTape,

          Cassir,

          Summa,

          SumNSP,

          "Disc_Sum",

          "MC_Date",

          "MC_Number",

          "MC_Depart",

          Status,

          StatusFiscal,

          Delivery,

          TransferReady,

          TransferComplete,

          ReturnNumber,

          CashKey,

          BuyerSum,

          DiscPercent,

          UpdateCounter,

          fsDocNumber,

          DefDiscount,

          FRNumber;

      SQL SELECT Id,

          Taken,

          Operation,

          ShopIndex,

          ShopIndexKKM,

          OperDay,

          "Cash_Code",

          NSmena,

          DateOperation,

          "Ck_Number",

          ControlTape,

          Cassir,

          Summa,

          SumNSP,

          "Disc_Sum",

          "MC_Date",

          "MC_Number",

          "MC_Depart",

          Status,

          StatusFiscal,

          Delivery,

          TransferReady,

          TransferComplete,

          ReturnNumber,

          CashKey,

          BuyerSum,

          DiscPercent,

          UpdateCounter,

          fsDocNumber,

          DefDiscount,

          FRNumber

      FROM SES.dbo.ChequeHead;

       

      LOAD Id as [ChequePos],

          Taken,

          ChequeId  as [ChequeHeadID],

          "Ch_Pos",

          Quant,

          Price,

          "Ck_Curs",

          "Ck_CurAbr",

          GrCode,

          Code,

          NDSx1,

          NDSx2,

          Summa,

          SumNSP,

          NSPPrice,

          SalesMan,

          BarCode,

          BarCode1,

          "Disc_Summ",

          "Disc_SummNSP",

          CashKey,

          SizeScale,

          SizeScaleValue,

          PosSign,

          PosFound,

          PriceWithoutDisc,

          NSPPriceWithoutDisc,

          PriceCur,

          DiscPercent,

          GoodsUnit,

          GoodsName,

          UpdateCounter,

          PlaceNumber,

          ReservoirNo,

          NozzleNo,

          SessionId,

          FreeMode,

          DateOperation;

      SQL SELECT Id,

          Taken,

          ChequeId,

          "Ch_Pos",

          Quant,

          Price,

          "Ck_Curs",

          "Ck_CurAbr",

          GrCode,

          Code,

          NDSx1,

          NDSx2,

          Summa,

          SumNSP,

          NSPPrice,

          SalesMan,

          BarCode,

          BarCode1,

          "Disc_Summ",

          "Disc_SummNSP",

          CashKey,

          SizeScale,

          SizeScaleValue,

          PosSign,

          PosFound,

          PriceWithoutDisc,

          NSPPriceWithoutDisc,

          PriceCur,

          DiscPercent,

          GoodsUnit,

          GoodsName,

          UpdateCounter,

          PlaceNumber,

          ReservoirNo,

          NozzleNo,

          SessionId,

          FreeMode,

          DateOperation

      FROM SES.dbo.ChequePos;

       

      LOAD Id as [ChequeDiscID],

          PosId  as [ChequePosID],

          Taken,

          DiscType,

          DiscId,

          CardNumber,

          DiscSumm,

          DiscSummNSP,

          CashKey,

          Quantity,

          DiscQueue,

          ApplyToPos,

          DiscPercent,

          Price,

          NSPPrice,

          UpdateCounter;

      SQL SELECT Id,

          PosId,

          Taken,

          DiscType,

          DiscId,

          CardNumber,

          DiscSumm,

          DiscSummNSP,

          CashKey,

          Quantity,

          DiscQueue,

          ApplyToPos,

          DiscPercent,

          Price,

          NSPPrice,

          UpdateCounter

      FROM SES.dbo.ChequeDisc;

       

      LOAD Id as [ChequePay],

          Taken,

          ChequeId  as [ChequeHeadID],

          DocType,

          DocDate,

          DocNumber,

          DocSumm,

          Status,

          PayType,

          CashKey,

          PaySumCur,

          CurCode,

          UpdateCounter,

          PayDocRate;

      SQL SELECT Id,

          Taken,

          ChequeId,

          DocType,

          DocDate,

          DocNumber,

          DocSumm,

          Status,

          PayType,

          CashKey,

          PaySumCur,

          CurCode,

          UpdateCounter,

          PayDocRate

      FROM SES.dbo.ChequePay;