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;
____________________________________________________________________
I suggest to implement a multi-layer data-architecture with multiple qvd's as intermediate steps and with at least 3 layers (generators --> datamodels --> reports) and applying incremental load-approaches to all or at least the biggest loadings. Further all big/important/particular loadings should get a separate generator.
Within the first link-area from More advanced topics of qlik datamodels and the last two link-areas from Advanced topics for creating a qlik datamodel you will find a lot of stuff what is meant and how it could be done.
Beside increasing the performance you will get a more stable solutions and maintaining and error-handling will be easier (your errors might be caused by temporary lost connections or blocked tables or something similar).
If possible you should avoid using link-table approaches and developing your datamodels in the direction of star-schemes which are mostly the best compromise from a developing point of view and performance.
- Marcus
Check this:
1. Don't load huge data, initiate it with debugging to see issues on data load.
2. Map data from different tables.
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 ).
Store the raw extracts, SQL into QVD's first.. as is
Read those QVD's, transform and make your keys/model
Store the transformed tables into QVD's again
Read those QVD into your App or read thoe whole model as Binary load into your final app (much faster)
2- sometimes there is an error in the middle of loading (unknown error ) , but if i reload it again it will work .
de-bug the load by limiting it to only 100 or run the task in QMC which would generate a log wfile/has an error in it...
Use that error as starting point to troubleshoot.
3- am not sure about linking tables if its in correct or there is another way to do it .
I'm not sure too but depends on requirements and what are your keys etc
4- date Range for invoice .
Use interval match, again depends on your requirement
etc...