Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
majid_ghaboli
Contributor II
Contributor II

How can I Make Static Dimension

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

This works, but I am looking for a more efficient and one-stop

thanks for your guide

1 Reply
marcus_sommer

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