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: 
sivakrishna
Contributor II
Contributor II

Back end data transformations

Hi i would like to derive new field  that give the number for my data by  depending upon given field or first field

i have data like this 
Year,  Quarter,  Sales


2016,  Q1,  234
2016,  Q2,  345
2016,Q3,  765
2017,Q2, 897
2017,Q3, 234       
2018,  Q1, 123

my target is to derive like

Year,  Quarter,  Sales, row
2016,  Q1,  234, 1
2016,  Q2,  345, 2
2016,Q3,  765, 3
2017,Q2, 897, 1
2017,Q3, 234 , 2      
2018,  Q1, 123, 1

or


Quarter, Year,   Sales,Row
 Q1,  2016,   234, 1
Q1,  2018,  123,2
Q2,   2016,   345, 1
Q2,   2017,   897, 2
Q3,   2016,  765, 1
Q3,  2017,   234 , 2  

 

1 Solution

Accepted Solutions
anushree1
Specialist II
Specialist II

Hi,

Please check if the below script works

Tab1:
load *,
AutoNumber(rowno(),Year) as Row;
load * Inline
[Year,Quarter,Sales
2016,Q1,234
2016,Q2,345
2016,Q3,765
2017,Q2,897
2017,Q3,234
2018,Q1,123];

View solution in original post

6 Replies
Edvin
Creator
Creator

Could you explain a little bit more? What's the pattern here?
Do you rank it by quarters, by years and quarters? Does Sales have to do anything?
sivakrishna
Contributor II
Contributor II
Author

I need rank for quarters of every year like i mentioned example below in my
text

Edvin
Creator
Creator

So in the second example:

Quarter, Year, Sales,Row
Q1, 2016, 234, 1
Q1, 2018, 123,2
Q2, 2016, 345, 1 (It should be row = 2, because of the Q2 in the 2016 year?)
Q2, 2017, 897, 2
Q3, 2016, 765, 1 (This should be row=3, because Q3 in the 2016 year??)
Q3, 2017, 234 , 2

sivakrishna
Contributor II
Contributor II
Author

Hi edvin

 Thanks for your reply

Actually i  have data like first table and i would like to derive fourth column like in 2nd table(i need like) .  Here how to get fourth column in back end

Edvin
Creator
Creator

I'm sorry, but I really don't understand properly what do you want. The 2nd table that you presented has no logic (Or I'm just not understanding). But I'm guessing I'll try:


You can create a measure with the expression:

=aggr(RowNo(),Year,Quarter)


Or if using back-end (as the topic suggests)


LOAD
Year,
Quarter,
Sales,
AutoNumber(RowNo(), Year) as Row
FROM [...]

Hope this helps you.

anushree1
Specialist II
Specialist II

Hi,

Please check if the below script works

Tab1:
load *,
AutoNumber(rowno(),Year) as Row;
load * Inline
[Year,Quarter,Sales
2016,Q1,234
2016,Q2,345
2016,Q3,765
2017,Q2,897
2017,Q3,234
2018,Q1,123];