Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Afternoon,
First post on these forums as I look to expand the use of QLIK within our organization. We are in the process of importing some data from CSV, and need to identify each line in the order with a record number which isn't being provided from the data source.
The CSV file looks like this:
Order Number | SKU | Line Number |
2220799 | Item 1 | |
2220799 | Item 2 | |
2220799 | Item 3 | |
2220799 | Item 4 | |
2220799 | Item 5 | |
2220799 | Item 6 | |
2220799 | Item 7 | |
2220799 | Item 8 | |
2220799 | Item 9 | |
2221424 | Item 1 | |
2221424 | Item 2 | |
2221424 | Item 3 |
When the import is occurring to QLIK Sense via the data load, we need to add a an ERP Line Order Number, which identifies the SKU's line number on the Order Number. EG:
Order Number | SKU | Line Number |
2220799 | Item 1 | 1 |
2220799 | Item 2 | 2 |
2220799 | Item3 | 3 |
2220799 | Item 4 | 4 |
2220799 | Item 5 | 5 |
2220799 | Item 6 | 6 |
2220799 | Item 7 | 7 |
2220799 | Item 8 | 8 |
2220799 | Item 9 | 9 |
2221424 | Item 1 | 1 |
2221424 | Item 2 | 2 |
2221424 | Item 3 | 3 |
The line number relates to the position on the order, so needs to reset each time the Order Number changes.
With other programming I would do this defining a variable within the load, but from my reading this isn't supported in QLIK.
Any assistance in how we can achieve this would be appreciated.
May be like this:
LOAD [Order Number],
SKU,
AutoNumber(RowNo(), [Order Number]) as [Line Number]
FROM .....
May be like this:
LOAD [Order Number],
SKU,
AutoNumber(RowNo(), [Order Number]) as [Line Number]
FROM .....
Hi Sunny,
Thankyou for the speedy reply.
Initial testing indicates this seems to be working! I will do some more detailed testing and revert back.
Thankyou once again
For future development, do learn about auto number.