topic Re: consolidate files based on group column in QlikView

consolidate files based on group column

userid128223 — Wed, 07 Dec 2016 17:40:07 GMT

Hi Everyone

I have input excel file with following info.

Course#	Student#
S123	456
P245	334
S123	S545
P245	D345
Z556	5667
S123	S23245
Z556	T0343567

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

Re: consolidate files based on group column

userid128223 — Wed, 07 Dec 2016 22:23:06 GMT

Anyone with solution

Re: consolidate files based on group column

tamilarasu — Thu, 08 Dec 2016 03:57:28 GMT

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.

Re: consolidate files based on group column

userid128223 — Thu, 08 Dec 2016 19:34:12 GMT

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.

Re: consolidate files based on group column

tamilarasu — Fri, 09 Dec 2016 01:43:18 GMT

Could you please post your input files and expected output?

Re: consolidate files based on group column

userid128223 — Fri, 09 Dec 2016 03:55:43 GMT

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

Re: consolidate files based on group column

tamilarasu — Fri, 09 Dec 2016 04:17:38 GMT

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.

Re: consolidate files based on group column

userid128223 — Fri, 09 Dec 2016 16:41:07 GMT

Thanks Tamil

Attached is sample files.

Re: consolidate files based on group column

tamilarasu — Sat, 10 Dec 2016 10:24:09 GMT

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.

Re: consolidate files based on group column

userid128223 — Sat, 10 Dec 2016 18:28:17 GMT

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.

StudentName	Grades
XYZ50	67
XYZ51	52
XYZ52	98
XYZ53	62
XYZ54	82
StudentName	Grades
XYZ55	67
XYZ56	52
XYZ57	98
XYZ58	62
XYZ59	82

Re: consolidate files based on group column

settu_periasamy — Sat, 10 Dec 2016 18:44:42 GMT

try to change the field names like below screen shot.

Re: consolidate files based on group column

tamilarasu — Sat, 10 Dec 2016 19:11:38 GMT

Hello Ami,

My bad. Settu has pointed out my mistake. Let us know If there is any issues.

Hi Settu,

Thanks for correcting me

Re: consolidate files based on group column

userid128223 — Tue, 13 Dec 2016 22:40:37 GMT

Thank you very much Tamil & Settu

Re: consolidate files based on group column

userid128223 — Wed, 14 Dec 2016 13:13:31 GMT

Hi Tamil

One question on your solution.

Let vFileDate = Peek('Temp',0,'Table');

what was the purpose of doing above in the script?

Re: consolidate files based on group column

tamilarasu — Thu, 15 Dec 2016 14:13:24 GMT

Hello Ami,

Kindly check the word document for understanding.

Re: consolidate files based on group column

tamilarasu — Thu, 15 Dec 2016 14:49:37 GMT

If you want to generate output files on a daily basis, you should make a small change in the script. Attached is the solution for it.

Kindly check and let me know If you have any questions or clarifications on this.