topic Re: Substracting two rows in QlikView

Substracting two rows

Anonymous — Fri, 15 Jun 2018 22:04:13 GMT

Hi,

I have an requirement which says to subtract the two date's data and stored in a new column.

Eg For Supplier

5/14 7 -

5/21 7 7-7 = 0

5/28 7 7-7 = 0

Attached is the excel file.

Thanks in advance!!

Thanks and Regards,

Drishti Goel

Re: Substracting two rows

Frank_Hartmann — Sun, 17 Jun 2018 08:37:57 GMT

Script:

LOAD MFG,
     MPN,
     [Customer Part Number],
     Date,
     Arrow,
     Supplier,
     Arrow - Supplier as Diff
FROM

(ooxml, embedded labels, table is Sheet1);

Output:

But as an alternative you can also make the calculation in a straight stable with Date as dimension and an expression:

sum(Arrow) - sum(Supplier)

Re: Substracting two rows

MarcoWedel — Sun, 17 Jun 2018 18:36:07 GMT

Within your sample data there are multiple fields containing a value of 7 in multiple consecutive rows.

Do you have a more unambiguous example?

Re: Substracting two rows

Anonymous — Mon, 18 Jun 2018 16:55:54 GMT

hey thanks for your time to reply for my post. My requirement is to find the out the difference of the values among the rows of the date for each supplier and arrow separately, You can refer to the column Q and R.

Re: Substracting two rows

Anonymous — Mon, 18 Jun 2018 17:01:50 GMT

Eg :

Date Parts Arrow Supplier Weekly Change

Arrow Supplier

6/12 1 7 8 0 0

6/18 2 7 8 7-7=0 8-8=0

6/24 3 8 9 8-7=1 9-8=1

Re: Substracting two rows

Anonymous — Mon, 18 Jun 2018 17:03:08 GMT

We're trying to find out the weekly trend and that's the reason why it has more ambiguous data

Re: Substracting two rows

MarcoWedel — Mon, 18 Jun 2018 19:57:52 GMT

Hi,

now that you clarified with another example one solution might be:

table1:
LOAD RecNo() as ID,
    MFG,
    MPN,
    [Customer Part Number],
    Date#(Date,'M/D') as Date,
    Arrow,
    Supplier,
    If(MFG&'/'&MPN=Previous(MFG&'/'&MPN),Arrow-Previous(Arrow),0) as [Weekly Arrow],
    If(MFG&'/'&MPN=Previous(MFG&'/'&MPN),Supplier-Previous(Supplier),0) as [Change Supplier]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1503036-328924/datasets_1.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

Re: Substracting two rows

Anonymous — Mon, 18 Jun 2018 20:07:01 GMT

Thank you, this is exactly I want it

Re: Substracting two rows

MarcoWedel — Mon, 18 Jun 2018 20:08:53 GMT

glad it worked for you.