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: 
BootCam
Creator
Creator

Problem Building a logic around getting values from same field

Hi All,


I am having difficult time to figure out a problem. Please see the attach file and below description of the problem.


When a Previous ID is Upgraded or downgraded to Current ID, the Sales Type is either U or D and Sales Price is for the current ID. But the ID which is Previous ID now they were once Current ID and they are included in the current ID and their Sales Type is A. My problem is to get the Downgraded Sales Amount by subtracting the Sales Price Attached to the Current ID from the Sales Price Attached to the Previous ID. For example, The downgraded amount for Current ID 155 is, (157-150) = 7 

I am struggling to build the logic for this problem. Can anybody shed some light on it?

Thanks

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hello,

Try this in the script:

Data:

LOAD [Previous ID],

     [Current ID],

     [Sales Price],

     [Sales Type]

FROM

Book7.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD

[Current ID] as [Previous ID],

[Sales Price] as [Previous Price]

Resident Data;

we get this for Current ID 155:

Current ID Previous ID [Sales Price] [Previous Price] Diff
155111150157-7

Regards

Andrew

View solution in original post

1 Reply
effinty2112
Master
Master

Hello,

Try this in the script:

Data:

LOAD [Previous ID],

     [Current ID],

     [Sales Price],

     [Sales Type]

FROM

Book7.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD

[Current ID] as [Previous ID],

[Sales Price] as [Previous Price]

Resident Data;

we get this for Current ID 155:

Current ID Previous ID [Sales Price] [Previous Price] Diff
155111150157-7

Regards

Andrew