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: 
userid128223
Creator
Creator

consolidate files based on group column

Hi Everyone

I have input excel file with following info.

Course#
Student#
S123456
P245334
S123S545
P245D345
Z5565667
S123S23245
Z556T0343567

I have script that generates individual files for all student# in .txt format. However my requirement is to create 1 file combined based on by associated Course#.

For example. My script create individual files like below for each student#.

456YYYYMMDD.txt

S545YYYYMMDD.txt

S23245YYYYMMDD.txt

I need to combined the output of each student# based on their course#.

output of above should be combined into 1 file and file named S123.txt

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Hi Ami,

Ah, Now I understood. If you would have attached a sample file, you might have got the solution two days before itself. Please check the attached file. Have a nice weekend.

View solution in original post

15 Replies
userid128223
Creator
Creator
Author

Anyone with solution

tamilarasu
Champion
Champion

Hi Amiumi,

Try this,

Data:
LOAD * INLINE [
Course#, Student#
S123, 456
P245, 334
S123, S545
P245, D345
Z556, 5667
S123, S23245
Z556, T0343567
]
;

UniqueCourse:
Load Distinct  Course#
Resident Data;

FOR i=1 to NoOfRows('UniqueCourse');
LET vCourse=FieldValue('Course#',$(i));
NoConcatenate
Table:
Load *
Resident Data Where [Course#] = '$(vCourse)';

STORE Table into $(vCourse).txt (txt);
DROP Table Table;


NEXT


DROP Table Data, UniqueCourse;


Sample qvw file attached for your reference.

userid128223
Creator
Creator
Author

Thanks for the reply.

It works partially. I need to merge content of below Student files based on their course.

456YYYYMMDD.txt

S545YYYYMMDD.txt

S23245YYYYMMDD.txt

I need to concatenate 3 above file based on their Course# match.

tamilarasu
Champion
Champion

Could you please post your input files and expected output?

userid128223
Creator
Creator
Author

Files are generated daily by another process in below format. These are text file with lot of details.

45620161101.txt

S54520161101.txt

S2324520161101.txt

D34520161101.tx

33420161101.txt


These files now need to be merged into 1 text file based on their Course#. Course & Student number input file has matching details. It shows what Student matches to what course.


Base on that there will be 2 outputfiles.

45620161101.txt

S54520161101.txt

S2324520161101.txt  

----------------------------

outputfile --> S12320161101.txt

D34520161101.tx

33420161101.txt

----------------------------

outputfile --> P24520161101.txt






tamilarasu
Champion
Champion

I created a sample file with the above data and attached the same. I have considered comma as delimeter in the case. If your input file delimeter is tab, then change it as delimiter is '\t'. Paste the input files in the input folder and run the QV application. You can see output files in output folder. Test the app with your real data.

If this is not what you want, please attach some sample input text files. It would be really easy for us to give the solution.

userid128223
Creator
Creator
Author

Thanks Tamil

Attached is sample files.

tamilarasu
Champion
Champion

Hi Ami,

Ah, Now I understood. If you would have attached a sample file, you might have got the solution two days before itself. Please check the attached file. Have a nice weekend.

userid128223
Creator
Creator
Author

Thanks Tamil for your effort

I still don't think i am getting desired output. The content of the files needs to be merged for the final output.

For example output for "P24520161101.txt" will be.

StudentNameGrades
XYZ5067
XYZ5152
XYZ5298
XYZ5362
XYZ5482
StudentNameGrades
XYZ5567
XYZ5652
XYZ5798
XYZ5862
XYZ5982