Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kazuohosoya
New Contributor II

How do I change the order of data items?

Everyone

Please tell me how to change the format of A to B

I would like to convert data to display a monthly graph. How do I write a Load script?

■Format A

Startdate  Location Num Task           period   Cost

201408     Osaka      1  basic design        10     $1,000

201506     Osaka      1  final design         6     $1,200

201511     Osaka      1  Design supervision  12     ¥3,600

201701     Nagoya     2  basic design         5       $250

201706     Tokyo      3  Design supervision   5     $5,000

201612     Osaka      4  basic design         4     $1,200

201704     Osaka      4  final design         5    $10,000

■Format B

date    Location  Numコード Task      Cost

201408  Osaka   1     basic design   $100

201409  Osaka   1     basic design   $100

201410  Osaka   1     basic design   $100

201411  Osaka   1     basic design   $100

201412  Osaka   1     basic design   $100

201501  Osaka   1     basic design   $100

201502  Osaka   1     basic design   $100

201503  Osaka   1     basic design   $100

201504  Osaka   1     basic design   $100

201505  Osaka   1     basic design   $100

201506  Osaka   1     final design   $200

201507  Osaka   1     final design   $200

201508  Osaka   1     final design   $200

201509  Osaka   1     final design   $200

201510  Osaka   1     final design   $200

201511  Osaka   1     final design   $200

201512  Osaka   1     Design supervision   $300

201601  Osaka   1     Design supervision   $300

201602  Osaka   1     Design supervision   $300

201603  Osaka   1     Design supervision   $300

201604  Osaka   1     Design supervision   $300

201605  Osaka   1     Design supervision   $300

201606  Osaka   1     Design supervision   $300

201607  Osaka   1     Design supervision   $300

201608  Osaka   1     Design supervision   $300

201609  Osaka   1     Design supervision   $300

201610  Osaka   1     Design supervision   $300

201611  Osaka   1     Design supervision   $300

201701  Nagoya   2     basic design   $50

201702  Nagoya   2     basic design   $50

201703  Nagoya   2     basic design   $50

201704  Nagoya   2     basic design   $50

201705  Nagoya   2     basic design   $50

201706  Tokyo   3     Design supervision   $1,000

201707  Tokyo   3     Design supervision   $1,000

201708  Tokyo   3     Design supervision   $1,000

201709  Tokyo   3     Design supervision   $1,000

201710  Tokyo   3     Design supervision   $1,000

201612  Osaka   4     basic design   $300

201701  Osaka   4     basic design   $300

201702  Osaka   4     basic design   $300

201703  Osaka   4     basic design   $300

201704  Osaka   4     final design   $2,000

201705  Osaka   4     final design   $2,000

201706  Osaka   4     final design   $2,000

201707  Osaka   4     final design   $2,000

201708  Osaka   4     final design   $2,000

10 Replies

Re: How do I change the order of data items?

Hi, here is a sample.

data:

LOAD * inline [

Startdate,Location,Num,Task,period,Cost

201408,Osaka,1,basic design,10,1000

201506,Osaka,1,final design,6,1200

201511,Osaka,1,Design supervision,12,3600

201701,Nagoya,2,basic design,5,250

201706,Tokyo,3,Design supervision,5,5000

201612,Osaka,4,basic design,4,1200

201704,Osaka,4,final design,5,10000

];

For vRow=0 to NoOfRows('data')-1

LET vStartDate = Peek('Startdate', $(vRow), 'data');

LET vLocation = Peek('Location', $(vRow), 'data');

LET vNum = Peek('Num', $(vRow), 'data');

LET vTask = Peek('Task', $(vRow), 'data');

LET vPeriod = Peek('period', $(vRow), 'data');

LET vCost = Peek('Cost', $(vRow), 'data');

processed:

LOAD

Date(AddMonths(Date#('$(vStartDate)', 'YYYYMM'), RecNo()), 'YYYYMM') as StartDate,

'$(vLocation)' as Location,

'$(vNum)' as Num,

'$(vTask)' as Task,

$(vCost)/$(vPeriod) as Cost

AutoGenerate $(vPeriod);

NEXT

DROP Table data;

kazuohosoya
New Contributor II

Re: How do I change the order of data items?

Thank you very much.

I checked the sample on the desk.

Please give me some time to test it in real environment.

kazuohosoya
New Contributor II

Re: How do I change the order of data items?

Thank you very much.

Please give me some time to test it in real environment.

MVP
MVP

Re: How do I change the order of data items?

The WHILE clause of the LOAD statement can be used to good effect for this scenario and using the IterNo() function you can create the necessary number of result rows for each source row.

2018-01-19 22_39_38-Edit Script [C__Users_Petter_Downloads_# QC 2018-01-19 Expand rows.qvw_].png

Here is how it looks like in Qlik Sense - the load script is 100% identical:

2018-01-19 22_42_57-.png

kazuohosoya
New Contributor II

Re: How do I change the order of data items?

Thank you very much


Sorry for the basic thing.

Please tell me how to execute the next For, depending on the value while executing the processing in For ~ Next. Specifically, if the data to be imported can not be divided by 0 in the following cases(※1), we want to move to the next For without executing AutoGenerate.

In other languages, you can exit the For statement with Break, EXIT, etc, but I do not know the syntax of QlikView. Please tell me sorry about basic things.

data:

LOAD * inline [

Startdate,Location,Num,Task,period,Cost

201408,Osaka,1,basic design,10,1000

201506,Osaka,1,final design,6,1200

201511,Osaka,1,Design supervision,12,3600

201701,Nagoya,2,basic design,5,250

201706,Tokyo,3,Design supervision,5,5000

201612,Osaka,4,basic design,4,1200

,,,,,0  // ← ※1

201704,Osaka,4,final design,5,10000

];

For vRow=0 to NoOfRows('data')-1

LET vStartDate = Peek('Startdate', $(vRow), 'data');

LET vLocation = Peek('Location', $(vRow), 'data');

LET vNum = Peek('Num', $(vRow), 'data');

LET vTask = Peek('Task', $(vRow), 'data');

LET vPeriod = Peek('period', $(vRow), 'data');

LET vCost = Peek('Cost', $(vRow), 'data');

processed:

LOAD

Date(AddMonths(Date#('$(vStartDate)', 'YYYYMM'), RecNo()), 'YYYYMM') as StartDate,

'$(vLocation)' as Location,

'$(vNum)' as Num,

'$(vTask)' as Task,

$(vCost)/$(vPeriod) as Cost

AutoGenerate $(vPeriod); // ← ※1

NEXT

DROP Table data;

MVP
MVP

Re: How do I change the order of data items?

Qlik has an EXIT FOR too - have a look at the documentation:

   For..next ‒ Qlik Sense

Re: How do I change the order of data items?

Hi, just to comment that I prefer the 'while' version posted by Petter than my answer. I didn't tested but most probably gives better performance, and avoids the requirement of managing variables. (Adding a new field to the table doesn't needs an additional variable to retrieve the value of each row.)

kazuohosoya
New Contributor II

Re: How do I change the order of data items?

Thank you for providing information.
Please give me time as I will try.

kazuohosoya
New Contributor II

Re: How do I change the order of data items?

It was successfully implemented. Thank you very much.