Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
____________________________________________________________________
