topic Re: How do I change the order of data items? in App Development

How do I change the order of data items?

Anonymous — Wed, 17 Jan 2018 14:03:07 GMT

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

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

rubenmarin — Thu, 18 Jan 2018 18:06:05 GMT

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;

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

Anonymous — Fri, 19 Jan 2018 00:18:17 GMT

Thank you very much.

I checked the sample on the desk.

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

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

Anonymous — Fri, 19 Jan 2018 00:22:01 GMT

Thank you very much.

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

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

petter — Fri, 19 Jan 2018 21:44:52 GMT

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.

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

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

Anonymous — Sat, 20 Jan 2018 13:33:53 GMT

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（※１）, 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 // ←　※１

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);　// ←　※１

DROP Table data;

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

petter — Tue, 23 Jan 2018 01:35:06 GMT

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?

rubenmarin — Tue, 23 Jan 2018 07:49:58 GMT

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.)

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

Anonymous — Tue, 23 Jan 2018 08:06:58 GMT

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

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

Anonymous — Thu, 25 Jan 2018 23:57:50 GMT

It was successfully implemented. Thank you very much.

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

petter — Fri, 26 Jan 2018 06:25:04 GMT

Please mark the question as helpful and correct to close this thread.