Create new table as per calculations in qliksense

[Rsaiq]

Hi All,

I am creating a table in qliksense that will take values from source table & store in new table.I have below table in the excel file.

Now i need to create a new table by taking min value,Max value from Stock field as per scriptID wise.My output should be like below in the qliksense .

Expalanation of above output :-

1.StockAmount(Min)
On 12/31/2008 (This is minimum date for 1001 scriptID) value is (977197-498400=478797) ,so we need to show this for min. stock value

2.StockAmount(Max)

on 12/31/2009 (This is maximun date for 1001 scriptID) value is -480814 ,so we need to show this for max. stock value

Same like this for 1002 & 1003 ID's.

I need to store the value in above table format in qliksense.

Please find the data in the attachment

Thanks in Advance

[Andrei_Cusnir]

Hello,

I believe that this can be achieved with a better and more efficient way that mine, however I will share with you my workaround, so that you could at least have a starting point.

1. I have created a table where I have only added SCRIPT_ID field

2. Then I have used the following measure expression for Min:

Pick(
  RowNo(), 
  Aggr(Sum({<Date={"$(=Min({<SCRIPT_ID={1001}>}Date))"}>}StockAmount), SCRIPT_ID),
  Aggr(Sum({<Date={"$(=Min({<SCRIPT_ID={1002}>}Date))"}>}StockAmount), SCRIPT_ID),
  Aggr(Sum({<Date={"$(=Min({<SCRIPT_ID={1003}>}Date))"}>}StockAmount), SCRIPT_ID)
)

3. And the following measure expression for Max:

Pick(
  RowNo(), 
  Aggr(Sum({<Date={"$(=Max({<SCRIPT_ID={1001}>}Date))"}>}StockAmount), SCRIPT_ID),
  Aggr(Sum({<Date={"$(=Max({<SCRIPT_ID={1002}>}Date))"}>}StockAmount), SCRIPT_ID),
  Aggr(Sum({<Date={"$(=Max({<SCRIPT_ID={1003}>}Date))"}>}StockAmount), SCRIPT_ID)
)

4. This gave me the following outcome:

As you can see it gives you the expected output, however this solution is definitely not a scalable one. In case you only have the mentioned IDs, or a couple more, then this solution will probably help you, however if you have too many IDs then, this requires a more complex investigation with a more complex set analysis most probably.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[Rsaiq]

Thanks @Andrei_Cusnir for reply and provided a solution

This is working fine with less ID's but i have around 1000 IDS.Could you please provide solution without giving Id's hard coded in the set analysis.

Thanks in Advance

[Andrei_Cusnir · Accepted solution]

Hello @Rsaiq,

I believe I was able to resolve it, however you will have to test it with many IDs to make sure that it helps you mitigate the issue.

In my use case scenario I had to create more columns with MinDate and MaxDate grouped by SCRIPT_ID. So here are the steps that I have followed:

1. I have the following script for loading data:

Data:
LOAD
    UniqueId,
    SCRIPT_ID,
    "Type",
    "Date",
    StockAmount,
    Account
FROM [...]
(...);

2. I have then created another table, which is merged with the other one when loaded:

MinMaxDates:
Load
    SCRIPT_ID,
    Date(Min("Date"), 'DD/MM/YYYY') as MinDate,
    Date(Max("Date"), 'DD/MM/YYYY') as MaxDate
Resident Data
Group By SCRIPT_ID;

3. I have added the SCRIPT_ID on a table and then I have used the following measure expressions:

Min:  =Aggr(Sum({<Date=P(MinDate)>}StockAmount), SCRIPT_ID)

Max :=Aggr(Sum({<Date=P(MaxDate)>}StockAmount), SCRIPT_ID)

4. The result is:

As you can see, none of the data is hard-coded and the result is the same as the previous workaround