Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Please Guide me
I have some Table of Supply Chain Management
as usual measurable variable of them is dynamic , In the other word when you want select specific Amount of Stock should be Type in Experssion ( for instance type if(Trstock_Type_Code=2 and Owner_Type=1 and Purch_Type=1 and Out_Type=1,TrStock_Amount) as Amnt_Wholesale_Gross ) and use similar Filter for dimension until Two sides of the equation to be correct
Such a calculation is time consuming and not efficient
How can I make it in Script ... Considering the fact that several table are as follows:
Invoice detail table :
Table name is TrStock :
LOAD "Good_Code", "Size_Code", "Size_Group_Code", "Stock_Code",
"TrStock_Amount", "Trstock_DiscPer_Corp", "Trstock_DiscPer_Shop",
"Trstock_No", "TrStock_Price", "TrStock_Quantity", "Trstock_Type_Code";
SQL SELECT "Good_Code", "Size_Code", "Size_Group_Code", "Stock_Code",
"TrStock_Amount", "Trstock_DiscPer_Corp", "Trstock_DiscPer_Shop",
"Trstock_No", "TrStock_Price", "TrStock_Quantity",
"Trstock_Type_Code"
FROM NikSport95.dbo.TrStock;
Invoice Header table :
Table name is HTrStock :
Inner Join ( TrStock )
LOAD Distinct "Disc_Amnt_Corp","PointM_Amnt", "PointP_Amnt",
"Disc_Amnt_Shop","Follow_Code", "Purcher_Code","Sender_Code",
"Follow_Commis_Amnt", "Inv_No", "Out_Type","FB_No","FB_Year",
"Pay_Date", "Pay_No","Sanad_SNo", "Save_Date",
"Save_Time", "Save_User", "Seller_Code", "Shop_Commis_Amnt",
"Stock_Code","Trstock_Date", "Trstock_No", "Trstock_Type_Code",
Confirm1,"Confirm1_Date", Confirm2, "Confirm2_Date", ConfirmL,"ConfirmL_Date",
"Rnd_Amnt", "Sum_Amnt","Out_Type" as "OutType_Code";
SQL SELECT "Disc_Amnt_Corp","PointM_Amnt", "PointP_Amnt",
"Disc_Amnt_Shop", "Follow_Code", "Purcher_Code","Sender_Code",
"Follow_Commis_Amnt", "Inv_No", "Out_Type","FB_No","FB_Year",
"Pay_Date", "Pay_No", "Sanad_SNo", "Save_Date",
"Save_Time", "Save_User", "Seller_Code", "Shop_Commis_Amnt",
"Stock_Code", "Trstock_Date", "Trstock_No", "Trstock_Type_Code",
Confirm1,"Confirm1_Date", Confirm2, "Confirm2_Date", ConfirmL,"ConfirmL_Date",
"Rnd_Amnt", "Sum_Amnt"
FROM NikSport95.dbo.HTrStock;
Out_Type :
left Join ( TrStock )
LOAD * Inline [
"Out_Type","Out_Type_Name"
1,"قطعی"
2,"انتقالی"
4,"هدیه"
5,"اسپانسری"
6,"ضایعات" ];
Purch_Type :
left Join ( TrStock )
LOAD * Inline [
"Purch_Type","Purch_Type_Name"
1,"عمده"
2,"فروشگاهی"
3,"شوروم"
4,"ضایعات" ];
Owner_Type :
left Join ( TrStock )
LOAD * Inline [
"Owner_Type","Owner_Type_Name"
1,"خود مالک"
2,"دیگر مالک" ];
The only solution I found was to create a database qvd ( In fact, a large flat table ) and then write my Condition
thanks for your guide
In general you need to collect all fields in a single table which should be included in a particular calculation. But this didn't mean mandatory that you need to create a wider table - you could also use a mapping with applymap() to include these fields: Mapping as an Alternative to Joining.
Beside them if your load-times are too large you should consider to load your data with an incremental approach. Here you will find varios links to this topic and some other useful stuff: Advanced topics for creating a qlik datamodel.
Another consideration would be not to do this within the script else within the gui with set analysis in your expressions, maybe something like this:
sum({< Trstock_Type_Code={2}, Owner_Type={1}, Purch_Type={1}, Out_Type={1}>} TrStock_Amount)
- Marcus