Please Help.

[Manobilly]

I have some doubts in AICT paper4 .I am posting them so you can help me.
Please JazakAllah khair

 

[Manobilly]

The very 1st task to extract the CCIssuer.In the ms they say to use the text to column and its not working,how do I solve it?

 

[Manobilly]

I don't know why but when I create the relationships and query ,no data shows.Can you tell me how to remove the duplicates?Plz Help

 

[sagar65265]

The very 1st task to extract the CCIssuer.In the ms they say to use the text to column and its not working,how do I solve it?

Here's a possible solution (courtesy my dad):

The assumed solution would be to convert the entire CCNumber column into integers (without decimal figures) and then use the LEFT() or RIGHT() functions to obtain the first 4 characters of each CCNumber. After that, you look up that value in the other file, and return the second column's contents:

=VLOOKUP(LEFT(J2,4), CCISSUERS.csv!$A$2:$B$6, 2, FALSE)

If this is what you've done and it shows an error, then this is a good bet to fix it:

When you use the LEFT() or RIGHT() functions to obtain a certain string of characters from another string, that's exactly what you get - you get a string of characters that do not act like numbers; in other words, you are telling the above compound function to look up a string of characters in a column of numbers; that is why you might get an #N/A message.

What you can do is convert this string you've split off into a number, which turns out to be surprisingly easy - you just add a +0 after the LEFT() function like so:

=VLOOKUP(LEFT(J2,4)+0,CCISSUERS.csv!$A$2:$B$6,2,FALSE)

Alternatively, you can use the VALUE function that converts a text string representing a number into a number as so:

=VLOOKUP(VALUE(LEFT(J2,4)), CCISSUERS.xlsx!$A$2:$B$6,2,FALSE)

(Note: if you are copying this formula from the post and pasting it into Excel, then you will HAVE to open the CCISSUERS.csv file for the reference to be accurate; Excel will not make understand where CCISSUERS.csv is, and so you have to open the file to ensure that Excel does indeed see it).

Try it out and see if it works, hopefully it will!

Best of Luck for all your exams!

 

[Manobilly]

Here's a possible solution (courtesy my dad):

The assumed solution would be to convert the entire CCNumber column into integers (without decimal figures) and then use the LEFT() or RIGHT() functions to obtain the first 4 characters of each CCNumber. After that, you look up that value in the other file, and return the second column's contents:

=VLOOKUP(LEFT(J2,4), CCISSUERS.csv!$A$2:$B$6, 2, FALSE)

If this is what you've done and it shows an error, then this is a good bet to fix it:

When you use the LEFT() or RIGHT() functions to obtain a certain string of characters from another string, that's exactly what you get - you get a string of characters that do not act like numbers; in other words, you are telling the above compound function to look up a string of characters in a column of numbers; that is why you might get an #N/A message.

What you can do is convert this string you've split off into a number, which turns out to be surprisingly easy - you just add a +0 after the LEFT() function like so:

=VLOOKUP(LEFT(J2,4)+0,CCISSUERS.csv!$A$2:$B$6,2,FALSE)

Alternatively, you can use the VALUE function that converts a text string representing a number into a number as so:

=VLOOKUP(VALUE(LEFT(J2,4)), CCISSUERS.xlsx!$A$2:$B$6,2,FALSE)

(Note: if you are copying this formula from the post and pasting it into Excel, then you will HAVE to open the CCISSUERS.csv file for the reference to be accurate; Excel will not make understand where CCISSUERS.csv is, and so you have to open the file to ensure that Excel does indeed see it).

Try it out and see if it works, hopefully it will!

Best of Luck for all your exams!

Thanks a lot to your dad and Thankyou .Sorry to bother you but I have another doubt can I ask?

 

[Manobilly]

In M/J 2010 for task 3 for finding whether the text book is required or not,I used Vlookup and If ,but its not working .

 

[sagar65265]

In M/J 2010 for task 3 for finding whether the text book is required or not,I used Vlookup and If ,but its not working .

If you look at the marking scheme, the following details are mentioned:

Book required lookup (C2:C10)
Value=$A$2 [1]
Source=Book Orders.csv [1]
Vector=a2:a21 [1]
Result=c-k2:c-k21 [2]
Use of if [1]

So, what I can gather from this is that you need to

i) look-up the contents of cell A2 (and use it as $A$2 so that even on copying down the formula the referencing is maintained; otherwise, the formula extended to C9 would be looking up the contents of cell A9, which would be empty. So, this is to maintain the referencing),

ii)look it up in Book Orders.csv which you should open in a NEW window (if you copy the contents of Book Orders.csv into a new workbook, then updating the data would be very dificult; it is better if you tell Excel to look up the value IN THE ORIGINAL FILE, which can be easily modified at any time),

iii)Give the range of the look-up as cells A2:A21 (these cells are in the Book Orders.csv file, and is the first column MINUS the heading "Student id"),

iv)Obtain a result from the range c-k2:c-k21 (which is the region where the "y" and empty cells are found),

v) Use "if" to ensure that the value "0" does not appear(if you just use the lookup function, then the result will either be "Y" or "0" - that's zero, not o! - and this is wrong; you need to use the "if" function to make sure that the result is not zero).

Without resorting to functions outside the syllabus, I can only guess that you need to manually tell Excel where to look-up from:

=IF(VLOOKUP($A$2, 'Book orders.csv'!$A$2:$K$21, 11)=0,"N", "Y")

This is to check for IDACOPT5, which is in the last column on Book Orders.csv.

For the next textbook(the next cell down), the formula would be

=IF(VLOOKUP($A$2, 'Book orders.csv'!$A$2:$K$21, 6)=0,"N", "Y")

This is to check for IDBC04, which is in cell C3 in the file you have to create, and is the 6th column in the Book Order.csv.
There was a formula to automatically do all this, but i'm not entirely sure which one it is - however, there doesn't seem to be a mark for using any outside formulas, so the formula above should be okay if you modify the returned column for each one.

The Examiner's Report says:

"It is gratifying to note the level of skill shown by candidates in pursuing this task. Many, however, may have
failed to get full recognition for a number of reasons. These include:

● the format of the table was not exactly as shown
● the data from the source files was copied to a range or to a new worksheet
● the formula view was printed after the “non-blank” filter was applied
● the “IF” formulae for the text book costs returned zeroes not blanks.

All of the above were common mistakes. In particular, it is worth Centres noting that in a business scenario,
source files will be updated regularly so candidates’ solutions should not include the need to manually copy
data to new worksheets. "

So what you need to do is ensure that the table you create is a carbon-copy of the one in the paper, you look-up values directly from the workbook, rather than copying the contents of Book Orders.csv into a new worksheet, ensure that all the formula are perfectly visible and none of them are blanked out, and make sure that the returned value is not "0" but "n".

Hope this helped!
Good Luck for all your exams!

 

[Manobilly]

If you look at the marking scheme, the following details are mentioned:

Book required lookup (C2:C10)
Value=$A$2 [1]
Source=Book Orders.csv [1]
Vector=a2:a21 [1]
Result=c-k2:c-k21 [2]
Use of if [1]

So, what I can gather from this is that you need to

i) look-up the contents of cell A2 (and use it as $A$2 so that even on copying down the formula the referencing is maintained; otherwise, the formula extended to C9 would be looking up the contents of cell A9, which would be empty. So, this is to maintain the referencing),

ii)look it up in Book Orders.csv which you should open in a NEW window (if you copy the contents of Book Orders.csv into a new workbook, then updating the data would be very dificult; it is better if you tell Excel to look up the value IN THE ORIGINAL FILE, which can be easily modified at any time),

iii)Give the range of the look-up as cells A2:A21 (these cells are in the Book Orders.csv file, and is the first column MINUS the heading "Student id"),

iv)Obtain a result from the range c-k2:c-k21 (which is the region where the "y" and empty cells are found),

v) Use "if" to ensure that the value "0" does not appear(if you just use the lookup function, then the result will either be "Y" or "0" - that's zero, not o! - and this is wrong; you need to use the "if" function to make sure that the result is not zero).

Without resorting to functions outside the syllabus, I can only guess that you need to manually tell Excel where to look-up from:

=IF(VLOOKUP($A$2, 'Book orders.csv'!$A$2:$K$21, 11)=0,"N", "Y")

This is to check for IDACOPT5, which is in the last column on Book Orders.csv.

For the next textbook(the next cell down), the formula would be

=IF(VLOOKUP($A$2, 'Book orders.csv'!$A$2:$K$21, 6)=0,"N", "Y")

This is to check for IDBC04, which is in cell C3 in the file you have to create, and is the 6th column in the Book Order.csv.
There was a formula to automatically do all this, but i'm not entirely sure which one it is - however, there doesn't seem to be a mark for using any outside formulas, so the formula above should be okay if you modify the returned column for each one.

The Examiner's Report says:

"It is gratifying to note the level of skill shown by candidates in pursuing this task. Many, however, may have
failed to get full recognition for a number of reasons. These include:

● the format of the table was not exactly as shown
● the data from the source files was copied to a range or to a new worksheet
● the formula view was printed after the “non-blank” filter was applied
● the “IF” formulae for the text book costs returned zeroes not blanks.

All of the above were common mistakes. In particular, it is worth Centres noting that in a business scenario,
source files will be updated regularly so candidates’ solutions should not include the need to manually copy
data to new worksheets. "

So what you need to do is ensure that the table you create is a carbon-copy of the one in the paper, you look-up values directly from the workbook, rather than copying the contents of Book Orders.csv into a new worksheet, ensure that all the formula are perfectly visible and none of them are blanked out, and make sure that the returned value is not "0" but "n".

Hope this helped!
Good Luck for all your exams!

Thanks a lot ,I don't know what to say,You helped a lot.Hope you pass in your exam with good grades.
Btw have you solved O/n/2013 I have doubt in the prompt for the birthday report how will we create it so it just looks for the month.
Oh and in O/N 2012 how do we remove the duplicates form the Venue and venue regions,I used remove duplicates in excel ,But when I created table in access and than a query no data was shown.
Thanks a lot for your help

 

[sagar65265]

Thanks a lot ,I don't know what to say,You helped a lot.Hope you pass in your exam with good grades.
Btw have you solved O/n/2013 I have doubt in the prompt for the birthday report how will we create it so it just looks for the month.
Oh and in O/N 2012 how do we remove the duplicates form the Venue and venue regions,I used remove duplicates in excel ,But when I created table in access and than a query no data was shown.
Thanks a lot for your help

I've done the 2012 O/N paper, so I can help out there - I can't find the 2013 October/November paper, or the marking scheme, or the supporting files, so i'm sorry on that front. If I do find them, i'll try my hand at it.

I'm not sure why you're having a problem with the importing, it's going on perfectly well here.

For the corrections, I went through the list by eye and changed LOEC to LOEA and NORC to NORA - that's all I did in both the tables (Note : The Marking Scheme says that you are NOT supposed to remove the duplicates, but you should rename them to a 4 character string, so that's really important), and then:

i) created a new database in Access;

ii) went to the "External Data" tab at the top, with all the import/export options;

iii) clicked on the "Text File" button in the Import section of the tab;

iv) went straight through the process, and

v) then the data was in the table.

I'm not sure why the data isn't appearing in your table; could you just try again and see if the steps you're taking are any different from the steps above?

All the data went into the query; i'm not sure what's happening on your side, could you post some screenshots of the relationship diagram and the design views of the query you've made?