Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
My Qlikview document is for shipping statistics, and my source table has duplicate lines. For example, we have SO# 123456. SO#123456 has 3 lines on it
SO# | Item # |
123456 | A |
123456 | B |
123456 | C |
This is fine for my QVD. However, I want to be able to add a third field which I will name NbrOfLines onto each row so that when I create any expressions using DISTINCT, I will know the amount of times this SO# is duplicated in the database.
Desired Result:
Max(Distinct SO#)
SO# | Item # | NbrOfLines |
123456 | A | 3 |
I have been doing a lot of searching of how to tackle this. Initially I was thinking an If expression in the load script, but I am having trouble with my approach. Perhaps constructing a variable would be useful. I have already added the RowNo() as LineNo into my load script, and in Pseudo code I am thinking If(SO# of LineNo(1)= SO# of LineNo(2) then sum the count of rows.) I am lost on how to structure at this point.
with these test data
test:
load * inline [
SO#, Item #
123456, A
123456, B
123456, C
1, A
1, B
2,A
2,B
2,C
2,D
];
you can get the result with an expression (red rectangle), there is no need to add a third field
if you still want to add the field
test:
load * inline [
SO#, Item #
123456, A
123456, B
123456, C
1, A
1, B
2,A
2,B
2,C
2,D
];
left join (test)
load SO#, count([Item #]) as NbrOfLines Resident test group by SO#;
with these test data
test:
load * inline [
SO#, Item #
123456, A
123456, B
123456, C
1, A
1, B
2,A
2,B
2,C
2,D
];
you can get the result with an expression (red rectangle), there is no need to add a third field
if you still want to add the field
test:
load * inline [
SO#, Item #
123456, A
123456, B
123456, C
1, A
1, B
2,A
2,B
2,C
2,D
];
left join (test)
load SO#, count([Item #]) as NbrOfLines Resident test group by SO#;
Thank you Massimo! Looks like I need to study more on the application of Total and Group By's in the load script. Thanks for the help.