Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to compare the records of a field and derive a new field based on the comparison.
Below is sample table;
Input_Table:
Unique_value, Text
10, A
20, P
10, B
10, C
20, Q
10, D
20, R
30, X
20, S
The desired table should look like this-
Output_Table:
Unique_value, New_Text
10, A
10, AB
10, ABC
10, ABCD
20, P
20, PQ
20, PQR
20, PQRS
30,X
I am not looking for hard-code solution as it could be derived easily through if-else or case statements. Please provide a generic solution.
Any help in approaching this problem will be appreciated.
Thanks.
You solution can be simplified by removing Table2. I think in general it is helpful to avoid unnecessary resident loads
Table1:
LOAD * INLINE [
Unique_value, Text
10, A
20, P
10, B
10, C
20, Q
10, D
20, R
30, X
20, S
];
Table2:
LOAD *,
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded;
Load *
Resident Table1
Order By Unique_value , Text;
Drop Table Table1;
If you don't want to sort by text and follow the actual order of Text, then this:
Table1:
LOAD * INLINE [
Unique_value, Text
10, A
20, P
10, B
10, C
20, Q
10, D
20, R
30, X
20, S
];
Table2:
LOAD *,
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded;
Load *
Resident Table1
Order By Unique_value; //Remove Text from the order by here
Drop Table Table1;
Try this ?
Table1:
LOAD * INLINE [
Unique_value, Text
10, A
20, P
10, B
10, C
20, Q
10, D
20, R
30, X
20, S
];
NoConcatenate
Table2:
LOAD *
Resident Table1
Order By Unique_value , Text asc;
Drop Table Table1;
NoConcatenate
Table3:
LOAD*,
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded;
Load *
Resident Table2;
Drop Table Table2;
You solution can be simplified by removing Table2. I think in general it is helpful to avoid unnecessary resident loads
Table1:
LOAD * INLINE [
Unique_value, Text
10, A
20, P
10, B
10, C
20, Q
10, D
20, R
30, X
20, S
];
Table2:
LOAD *,
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded;
Load *
Resident Table1
Order By Unique_value , Text;
Drop Table Table1;
If you don't want to sort by text and follow the actual order of Text, then this:
Table1:
LOAD * INLINE [
Unique_value, Text
10, A
20, P
10, B
10, C
20, Q
10, D
20, R
30, X
20, S
];
Table2:
LOAD *,
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded;
Load *
Resident Table1
Order By Unique_value; //Remove Text from the order by here
Drop Table Table1;
Oh Yes, I got that, but was little lazy to revamp the code. Just copied and pasted. 😉 later realized.
Thanks Sunny and Nagaraju, it worked.
Can you please explain the below statement
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded
1. How can the field 'TextAdded' is used inside the Peek() before it gets created.
2. I understood the rest of the logic of using the preceding load and the condition to compare the previous record values.
Please revert for the clarification.
I do not know if this explanation helps, probably sunny can put it in simple words if this doesnt help !!!!
Peek() is a Inter record function where it pulls records from previously loaded data. So it accumulates the information from Text field and puts the field value into that TextAdded Field and reiterates record by record and appends with Text field.
See Peek() example from Reference manual.
peek(fieldname [ , row [ , tablename ] ] )
Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.
Fieldname must be given as a string (e.g. a quoted literal).
Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.
If no row is stated, -1 is assumed.
Tablename is a table label, see Table Labels, without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.
Examples:
peek( 'Sales' )
returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).
peek( 'Sales', 2 )
returns the value of Sales from the third record read from the current internal table.
peek( 'Sales', -2 )
returns the value of Sales from the second last record read into the current internal table.
peek( 'Sales', 0, 'Tab1' )
returns the value of Sales from the first record read into the input table labeled Tab1.
Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.
May be this helps?
Iteration1:
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded --> TextAdded = A because the If statement fails and First text value is stored in TextAdded
Iteration2:
IF(Unique_value <> Previous(Unique_value), Text, Peek('TextAdded')& Text) AS TextAdded --> TextAdded = AB as prev value is same it will pick the next text value and appends the textadded
Iteration3: ........ and so on.
Thanks for drilling down to the exact logic.
The 2nd logic really helped in understanding the flow and with that the first iteration will always be false.
Regards
Anuj Koshti