Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thank you very much.
I checked the sample on the desk.
Please give me some time to test it in real environment.
Thank you very much.
Please give me some time to test it in real environment.
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:
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;
Qlik has an EXIT FOR too - have a look at the documentation:
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.)
Thank you for providing information.
Please give me time as I will try.
It was successfully implemented. Thank you very much.