Qlik Sense script error

steps1989 — Thu, 02 Aug 2018 12:27:21 GMT

Hello Experts,

am working on RetialPro9 system ( POS & Inventory management ) and we start using Qlik Sense couple months ago , am trying to make a scripts to load data from 15 table (invoice - inventory -vouchers - transfers - adjustment ..etc) but i have some issues in my script because am new in scripting , so i need some help regarding these issues please ...

issues :

1- loading data taking too much time because there is a huge date in our DB (if there is any way to load only the different date which is new ).

2- sometimes there is an error in the middle of loading (unknown error ) , but if i reload it again it will work .

3- am not sure about linking tables if its in correct or there is another way to do it .

4- date Range for invoice .

etc...

so kindly take a look on this script and am waiting for any advice

NOTE: i think there is another mistakes in my script so am waiting for experts advice.

Best Regards ...

HERE Section No 1 :

SECTION 1:

__________________________________________________________________

INVOICE:

LOAD *,

date(CREATED_DATE ,'MM/DD/YYYY') as INVC_CREATED_DATE,

year (CREATED_DATE) as INVCYEAR,

month (CREATED_DATE) as INVCMONTH,

week (CREATED_DATE) as INVCWEEK,

day (CREATED_DATE) as INVCDAYS;

select t.ITEM_SID,

i.SBS_NO as INVC_SBS,

i.STORE_NO as INVCSTORENO,

s.STORE_NO as STORENO,

s.STORE_CODE as STORECODE,

s.STORE_NAME as INVCSTORENAME,

i.INVC_NO,

i.CREATED_DATE,

i.INVC_SID,

i.INVC_TYPE,

i.DISC_PERC,

i.DISC_AMT,

i.CASHIER_ID,

to_char(i.CREATED_DATE) as INVCDATE,

t.ORIG_PRICE as INVCORIGPRICE,

t.PRICE as INVCPRICE,

t.ORIG_PRICE,

t.ORIG_TAX_AMT,

Round(t.TAX_AMT,2),

t.DISC_REASON_ID,

t.COST as INVCCOST,

case

when i.INVC_TYPE = 2

then t.QTY * - 1

else t.QTY

end as INVCQTY,

case i.INVC_TYPE

when 0 then 'REGULAR'

when 2 then 'RETURN'

else 'NA'

end as INVCTYPE

from INVOICE i

join invc_item t

on i.invc_sid = t.invc_sid

join SBS_STORE_LIST s

on i.STORE_NO = s.STORE_NO

where INVC_TYPE in (0, 2)

and i.REF_INVC_SID is null;

________________________________________________________

SECTION 2 :

INVOICETENDER:

LOAD * ;

select

t.INVC_SID,

t.TENDER_TYPE,

t.TENDER_NO,

t.TAKEN,

t.GIVEN,

t.AMT,

t.CURRENCY_NAME,

t.DOC_NO,

t.CRD_TYPE,

CRD_NAME

from INVC_TENDER t

left join CREDIT_CARD c

on c.CRD_TYPE = t.CRD_TYPE;

__________________________________________________________________

SECTION 3:

DISCOUNT_REASONS:

Load *;

Select

DISC_REASON_ID,

DISC_REASON_NAME

from DISC_REASON_V

where ACTIVE=1 and SBS_NO=1;

_________________________________________________________

SECTION 4:

INVN_QTY:

Load *;

Select

q.STORE_NO,

q.ITEM_SID,

q.QTY,

q.SOLD_QTY,

q.TRANSFER_IN_QTY,

q.TRANSFER_OUT_QTY,

q.STORE_NO as LIST_STR_NO,

q.TO_IN_SENT_QTY as FST_RCVD_QTY

from invn_sbs_qty_v q

where STORE_NO between 0 and 249

;

_____________________________________________

SECTION 5:

Load * ;

select

t.ITEM_SID,

t.TEXT1,

t.TEXT2,

t.TEXT3,

t.TEXT4,

t.TEXT5,

t.TEXT6,

t.TEXT7,

t.TEXT8,

t.TEXT9,

t.TEXT10

from INVN_SBS t

where SBS_NO=1;

_____________________________________________

SECTION 6:

UDF:

Load *;

Select

a.ITEM_SID,

a.UDF3_VALUE as Materials,

a.UDF4_VALUE as KIDS_G,

a.UDF5_VALUE as TEST5,

a.UDF6_VALUE as Ayman,

a.AUX1_VALUE as Features,

a.AUX2_VALUE as ColorName,

a.AUX3_VALUE as Dimension,

a.AUX4_VALUE as MadeIN,

a.AUX5_VALUE as UD_Disc,

a.AUX6_VALUE as Season,

a.AUX7_VALUE as MainColor,

a.AUX8_VALUE as SubColor

from INVN_SBS_SUPPL_UDF_V a;

_____________________________________________

SECTION 7:

VOU:

Load *;

Select

vi.QTY as VOU_QTY,

vi.COST as VOU_COST,

vi.PRICE as VOU_PRICE,

vi.ITEM_SID,

o.VOU_TYPE,

o.ARRIVED_DATE,

o.VERIFIED,

o.APPROV_DATE,

o.ASN_NO,

s.store_no AS VOU_STORE_NO,

s.store_code as VOU_STR_IN_CODE,

s.store_name as VOU_STR_IN_NAME,

CASE

when o.VOU_TYPE=1 then vi.QTY*-1

when o.VOU_TYPE=0 then vi.QTY*1

else 0

end CMP_RCVD_QTY,

case o.VOU_TYPE

when 0 then 'REGULAR'

when 1 then 'RETURN'

else 'NA'

end as VOUTYPE,

case o.VOU_CLASS

when 0 then 'Former'

when 1 then 'Bending'

when 2 then 'ASN'

else 'NA'

end as VOUCLASS,

CASE

when o.VEND_CODE is NULL

and o.TO_NO is NOT NULL

then vi.QTY

else 0

end TRANS_IN_QTY

from VOUCHER_V o

join VOU_ITEM_V vi

ON o.VOU_SID = vi.VOU_SID

join STORE_V s

ON o.store_no =s.store_no

where

o.REF_VOU_SID is NULL

and o.HELD=0

and o.ACTIVE=1

and o.SBS_NO=1

and s.store_no between 0 and 249

;

___________________________________________________

SECTION 8:

load *;

MARKDOWN_ITEM:

Select ITEM_SID, MARKDOWN_VALUE

from MARKDOWN_ITEM;

__________________________________________________

SECTION 9:

STORE_LIST:

Load *;

Select

s.STORE_NO,

s.STORE_CODE,

s.STORE_NAME

from STORE_V s

where STORE_NO between 0 and 249

and STORE_CODE not in ('031')

and SBS_NO=1;

__________________________________________________

SECTION 10:

DCS:

Load *;

Select

d.DCS_ID,

d.DCS_CODE,

d.D_NAME,

d.C_NAME,

d.S_NAME,

d.DEPARTMENT,

d.DEPARTMENT_NAME,

d.CLASS,

d.CLASS_NAME,

d.SUBCLASS,

d.SUBCLASS_NAME

from DCS_V d

where

DCS_ID not in(133,493)

and DCS_ID>1

and ACTIVE=1;

_______________________________________________________

SECTION 11:

ITEMD_DETAILS:

LOAD * ,

date(LST_RCVD_DATE ,'MM/DD/YYYY') as LAST_RCVD_DATE,

date(FST_RCVD_DATE ,'MM/DD/YYYY') as FIRST_RCVD_DATE,

date(UDF1_DATE ,'MM/DD/YYYY') as STOREIN_DATE,

date(LST_SOLD_DATE ,'MM/DD/YYYY') as LAST_SOLD_DATE,

year (LST_RCVD_DATE) as LSTRCVDYEAR ,

month (LST_RCVD_DATE) as LSTRCVDMONTH,

day (LST_RCVD_DATE) as LSTRCVDDAY,

year (LST_SOLD_DATE) as LSTSOLDYEAR ,

month(LST_SOLD_DATE) as LSTSOLDMONTH ,

day(LST_SOLD_DATE) as LSTSOLDDAY,

year (FST_RCVD_DATE) as FSTRCVDYEAR ,

month (FST_RCVD_DATE) as FSTRCVDMONTH,

day (FST_RCVD_DATE) as FSTRCVDDAY,

year (UDF1_DATE) as STOREIN_YEAR ,

month (UDF1_DATE) as STOREIN_MONTH,

day (UDF1_DATE) as STOREIN_DAY;

select

i.SBS_NO,

i.ITEM_SID,

i.STYLE_SID,

i.ITEM_NO,

i.ALU,

i.VEND_CODE,

i.DESCRIPTION1,

to_char(i.ITEM_SID) as ITEMSID,

to_char(i.STYLE_SID) as STYLESID,

i.DESCRIPTION2,

i.DESCRIPTION3,

i.DESCRIPTION4,

i.DCS_CODE,

i.ATTR,

i.SIZ,

i.COST,

i.LST_RCVD_DATE,

i.LST_SOLD_DATE,

i.FST_RCVD_DATE,

i.UDF1_DATE,

i.QTY_PER_CASE,

i.ACTIVE,

i.MARKDOWN_PRICE,

i.UDF2_VALUE as ITEM_STATUS

from

INVENTORY_V i

where

i.ACTIVE=1

and i.ITEM_SID not in(-1872510140613547019,-1842217402694314985)

and i.SBS_NO=1

;

_____________________________________________________________________

SECTION 12:

PRICES:

Load *;

Select

ITEM_SID,

PRICE_LVL,

PRICE

from INVN_SBS_PRICE

where SBS_NO=1 and PRICE_LVL=7;

____________________________________________________________________

topic Re: Qlik Sense script error in App Development

Qlik Sense script error

Re: Qlik Sense script error

Re: Qlik Sense script error

Re: Qlik Sense script error