topic Re: Need help in Qlikview scripting in QlikView

Need help in Qlikview scripting

pavandbs — Mon, 18 Jul 2016 08:38:52 GMT

Hello All,

I am brainstorming to get the desired output for the below requirement.

Transaction Date	Deal	Prev_Deal	Sales Margin	Derived Field	Status
30-Jul-16	12345	0	1000	1000	Dead
1-Aug-16	67890	12345	2000	1000	Dead
2-Aug-16	67891	67890	4000	3000	Dead
3-Aug-16	67892	67891	5000	4000	Dead
1-Sep-16	67889	67892	7000	2000	Live

I need to calculate the derived field based on Month

1. As of 30-Jul-2016 Transaction date I have reported 1000 Sales Margin. (As of 30-Jul-2016 Status is also only)

2. On 1-Aug-2016 67890 is inserted, deal 12345 is parent. Since for Jul I have reported 1000 for Aug I have to report 2000-10000=1000 is my derived field. For deal 67891,same as 67890. At the end of Aug I have reported 4000

3. On 01-Sep-16 since I have reported 4000, now I should report 7000-4000.

Please advice how I derive the logic. All the Deal and Prev_Deal are interlinked and are in one package.

Re: Need help in Qlikview scripting

marcus_sommer — Mon, 18 Jul 2016 10:24:35 GMT

I don't really understand what do you want to do but if you need to match respectively to calculate something depending on other records you could do it with: Peek() or Previous() ?

- Marcus

Re: Need help in Qlikview scripting

ToniKautto — Mon, 18 Jul 2016 23:43:35 GMT

Hi Pavan,

as Marcus mentioned the expected result is a bit unclear. Can you please elaborate on what you expect the result to be on each row in you example table?

If you attach a sample QVW file with the sample data loaded and a table object prepared for the aggregation, I also think you might get more precise answers to your inquiry.

Re: Need help in Qlikview scripting

pavandbs — Tue, 19 Jul 2016 02:53:16 GMT

Hi Toni & Marcus,

Thanks for your response.

The Column derived is my expected result column. Scenario is bit complex but I will try by best to explain

1. Basically end users are Sales Traders. Traders will book a deal lets say ForEx, for any adjustments they may cancel existing deal and re issue for the same package. Sales margin numbers are reporting by month

2. Deal and prev_deal are inter connected. Column Deal is Parent, prev_deal is Child. If Prev Deal is 0 means, that is the first one to insert.

3. Example, as of 30-Jul-16, Deal 12345 is Live. I will be reporting original sales Margin amount 1000 as my Derived field. Deal 12345 is a new deal as of 30-Jul-2016, prev_deal is zero

4. As of 01-Aug-2016, deal 12345 has been cancelled (Means deal 12345 is dead) and new deal has been reissued 67890. Means as of 01-Aug-2016 deal 67890 is live. Deal 12345 is parent deal for 67890. My logic for derived column is, since I have already reported for 1000 for previous month I should subtract previous month report number. i.e Sales Margin 2000(Reissued deal Sales Margin)-1000 (previous month reported sales Margin)

5. Same logic for remaining deals 67891, 67892. 67891 is re issued deal of 67890. Derived column should be 4000(newly reissued deal)-1000 (last month reported sales margin). Same logic applies for 67892.

6. At the end of the month I will be reporting 67892, because this deal would be live as of Aug-2016 and I will be reporting 5000-1000=4000

7. On the 01-Sep-2016 deal 67892 is Cancelled and Reissued to new deal 67889. Since Sales Margin is already reported for Aug, I need to take 67892 reported sales Margin 4000 and subtract from new sales margin for September. My derived column for 01-Sep-2016 deal is 7000-4000=3000 (Apologies I have given wrong calculation for Sep in my table ) .

Thanks,

Pavan

Re: Need help in Qlikview scripting

qlikview979 — Tue, 19 Jul 2016 05:08:55 GMT

Hi Pavan,

I am confused . Here I have one Doubt how you get 10000 you don't have this much amount in your data any where.

check in your mentioned Second point.

Since for Jul I have reported 1000 for Aug I have to report 2000-10000=1000 is my derived field

Re: Need help in Qlikview scripting

qliksus — Tue, 19 Jul 2016 05:23:26 GMT

Maybe something like this

A:
LOAD *,date(Date#(TransactionDate)) as TransactionDate1 INLINE [
    TransactionDate,Deal,Prev_Deal, SalesMargin,MonthDay,Month
    30-Jul-16, 12345, 0, 1000 , 0730,07
    01-Aug-16, 67890, 12345, 2000,0801,08
    02-Aug-16, 67891, 67890, 4000,0802,08
    03-Aug-16, 67892, 67891, 5000,0803,08
    01-Sep-16, 67889, 67892, 7000,0901,09
];

load *,
SalesMargin-Derived as NewDerived ;
load
*,
if( trim(Month)<>trim(Peek(Month)) ,numsum(fabs(Peek(SalesMargin)),0), peek(Derived)) as Derived,
if( trim(Month)<>trim(Peek(Month)),1, 2) as Derived1,
TextBetween(TransactionDate,'-','-') as month

Resident A
Order by MonthDay;

DROP Table A;

Re: Need help in Qlikview scripting

Anil_Babu_Samineni — Tue, 19 Jul 2016 07:30:53 GMT

Might be you are expecting similar to this

PFA.

Please try to post application with Expected O/P Then you will get proper information.

Re: Need help in Qlikview scripting

pavandbs — Wed, 20 Jul 2016 05:34:42 GMT

Sorry its a Typo. It should be 1000. I have posted another in detail.

Thanks,

pavan

Re: Need help in Qlikview scripting

pavandbs — Wed, 20 Jul 2016 08:02:31 GMT

Hi Toni,

Thanks for your response.

The Column derived is my expected result column. Scenario is bit complex but I will try by best to explain

2. Deal and prev_deal are inter connected. Column Deal is Parent, prev_deal is Child. If Prev Deal is 0 means, that is the first one to insert.

3. Example, as of 30-Jul-16, Deal 12345 is Live. I will be reporting original sales Margin amount 1000 as my Derived field. Deal 12345 is a new deal as of 30-Jul-2016, prev_deal is zero

6. At the end of the month I will be reporting 67892, because this deal would be live as of Aug-2016 and I will be reporting 5000-1000=4000

Re: Need help in Qlikview scripting

Anil_Babu_Samineni — Wed, 20 Jul 2016 08:45:54 GMT

Can you please send that Applicaton, Might be he is looking same thing

Re: Need help in Qlikview scripting

ToniKautto — Fri, 22 Jul 2016 05:10:31 GMT

Hi Pavan,

from my understanding there is not a one-step way to accomplish your requirement in the load script. I would suggest looking at flagging the data so that you can aggregate the number over each deal path and per month within the deal path.

In the attached example I have used the Hierarchy load prefix to group the deals over the first deal in the chain of deals. The date value then needs to be reformatted to a month to allow the second level of grouping. In this example I used Month Start to find what month each date belongs to.

With these two grouping you will have to find a way to aggregate the derived value as you expect. I am not sure exactly what the best approach for the aggregation would be.

Re: Need help in Qlikview scripting

pavandbs — Fri, 22 Jul 2016 05:38:12 GMT

Hi Toni,

Thanks a lot for quick turn around. Logic looks good, however when I reload Derive column is disappearing. Did I miss some thing ? I mean in script the couldn't see Derive column

Re: Need help in Qlikview scripting

ToniKautto — Sun, 31 Jul 2016 23:47:42 GMT

Hi Pavan,

sorry, I accidentally left the derive field in the sample, after trying to find a quick way to add it. As I mentioned above I am not able to find a simple solution to your required logic.

The main complexity I struggled with is that you need to aggregate per month within one deal path, and then carry over result to next month. I did not find a simple way to do that. My assumption is that you might need to flag the aggregated intervals and then aggregate the intervals. I think you mentioned trying to do this thought FOR loops or similar, have you tried that approach?