How can I compute only the first occurrence of field value?

[#1]

Hello QV community,

I'd be grateful if someone could help me with the following issue.

I have the following source file

ID_1	ID_2	VALUE_1	VALUE_2
AAA	111	500	500
AAA	222	500	500
BBB	333	600	600
BBB	444	600	600

I need to compute only the first occurrence of ID_1 during LOAD

So, I need to perform the operation only the first time ID_1=AAA.

Same thing for ID_1=BBB

ID_1	ID_2	VALUE_1	VALUE_2	[VALUE_1+VALUE_2]
AAA	111	500	500	1000
AAA	222	500	500	0
BBB	333	600	600	1200
BBB	444	600	600	0

LOAD DISTINCT ID_1 does not work in this situation, Is there any other way I can solve this?

Regards,

Leonardo

[whiteline]

Hi.

If you want to calculate the sum in the script use if() and previous() functions to check if the ID_1 value of the previous row is the same as the current row.

[#3]

Whiteline,

I really appreciate your prompt reply.

I'm afraid that I didn't publish the best example. Sorry, my fault.

The actual question would be "How I can compute only one occurence of field value?"

AAA values are not necessarily together, as depicted in example below.

One thing to consider: Even though ID_2 may change, the combination ID_1, VALUE_1 and VALUE_2 is always the same.

That is, whenever ID_1 is AAA, VALUE_1=500 and VALUE_2=500

ID_1	ID_2	VALUE_1	VALUE_2
AAA	111	500	500
AAA	222	500	500
BBB	333	600	600
BBB	444	600	600
AAA	555	500	500
BBB	666	600	600

[maxgro · Accepted solution]

a:

LOAD ID_1, ID_2, VALUE_1, VALUE_2

FROM [http://community.qlik.com/thread/138508] (html, codepage is 1252, embedded labels, table is @1);

a2:

NoConcatenate

LOAD ID_1, ID_2, VALUE_1, VALUE_2,

if(Peek(ID_1) <> ID_1, VALUE_1 + VALUE_2, 0) as V1V2

Resident a

Order by ID_1, ID_2;

DROP Table a;

[whiteline]

To place them together usually the order by statement is used. Then you can do what I've suggested above.

But why do you load the data in such a strange way ? Consider split the tables like this:

Load distinct ID_1, VALUE_1, VALUE_2 ...

Load ID_1, ID_2

Does it meet your requirements ?

[#6]

Leonardo

if(ID_1 <> previous(ID_1), VALUE_1 + VALUE_2 ,0) AS [VALUE_1+VALUE_2];

if the previous ID_1 is different to the ID_1 that you are reading, means that is the first occurrence, you should sort by ID_1 if you aren't sure if the data is sort by this field.

Hope it helps!

Best regards!

Jonathan

Remember to mark as answered if this is what you looking for!

[#7]

Leonardo

if(ID_1 <> previous(ID_1), VALUE_1 + VALUE_2 ,0) AS [VALUE_1+VALUE_2];

if the previous ID_1 is different to the ID_1 that you are reading, means that is the first occurrence, you should sort by ID_1 if you aren't sure if the data is sort by this field.

Hope it helps!

Best regards!

Jonathan

Remember to mark as answered if this is what you looking for!

[#8]

Brilliant! thank you!